I’m doing a COUNTIF and when I auto-fill the formula I’d like the sheet referred to for the range to change based on a text string in a cell. For example I could do this:
| A | B | C | |
|---|---|---|---|
| 1 | Sheet | Critical | Moderate |
| 2 | Sheet 2 | =COUNTIF(‘Sheet 2’!B:B,B$1) | |
| 3 | Sheet 3 | ||
| 4 | Sheet 4 |
But I would like to be able to reference A2 as a text string and have the formula take that input to use as the sheet for the COUNTIF range. I’ve tried what seemed obvious to me:
=COUNTIF(‘A2’!B:B,B$1) and =COUNTIF(‘TEXT(A2)’!B:B,Sheet1!B$1)
Have not had any luck figuring it out so thought I would check in here.
1