I have an INDIRECT formula in my google sheet.

Formula: =INDIRECT("B"&J4) --> this works without any issues.

I am however trying to define a range with it.

I am trying to get this result: B5:B --> Number 5 would be dynamically pulled from J4.

I tried entering =INDIRECT("B"&J4):B but that is giving me an error: 'Unknown range name: 'B'.'
If however I enter =INDIRECT("B"&J4):B10 for example, the formula will work and will pull the data correctly but I really need the range to be defined B5:B (five being dynamically pulled as described above).

I am hoping to use the here described indirect formula in COUNTIFS formula in order to dynamically define COUNTIFS range, for reference:

=IF(MONTH(B4:B)=8,COUNTIFS(B4:B,">="&B4, B4:B,"<="&EOMONTH(B4,0)),) 

=IF(MONTH(B4:B)=8,COUNTIFS(INDIRECT("B"&J4):B,">="&B4, B4:B,"<="&EOMONTH(B4,0)),)

but it is not working. Not sure if this a syntax error of if INDIRECT cannot be used to define a range.

CodePudding user response：

You can include the whole range as text inside the INDIRECT Function.

=INDIRECT("B"&J4&":B")