Use COUNTIF inside INDIRECT in Google Sheets

Question

I am trying to reference a range in a google sheet based on counting the number of not-empty rows in a different column.

I basically want to look up 'Jens Raw Data'!K:Kx, where x is the number of the last not empty row in column J. This is what I have tried:

=indirect("Jens Raw Data'!K"&COUNTIF('Jens Raw Data'!J:J,"<>"))

I get this error: Function INDIRECT parameter 1 value is 'Jens Raw Data'!K:K91'. It is not a valid cell/range reference.

Putting a TEXT around the countif did not work (or I could not figure out how to do it properly).

Any help appreciated.

Answer 1

Reference strings on INDIRECT should not use ', so instead of

=indirect("Jens Raw Data'!K"&COUNTIF('Jens Raw Data'!J:J,"<>"))

try

=indirect("Jens Raw Data!K"&COUNTIF('Jens Raw Data'!J:J,"<>"))