Use cell value in formula

[288enzo]

Hi all,

I'm stuck on how to use the value in a cell as the row reference in a formula. I believe I need to use indirect, just not sure how. Thank you.

This is my current formula

=COUNTIFS('KPI Data'!$D$2:$D$14400,C8,'KPI Data'!$M$2:$M$14400,"<="&(TODAY()-$J$1))

I am trying to replace "14400" with the value in K1.

 

[Fluff]

Why do you need to do that?

 

[felixstraube]

INDIRECT Returns the range of the text you give it as input.

=INDIRECT("'KPI Data'!A1:A100")

will return the range 'KPI Data'!A1:A100

So you can concat the desired text an pass it to INDIRECT

INDIRECT("'KPI Data'!D2:D"&K1)

But, if you give us the answer to Fluffs question, there may be a better solution to your problem.

 

[Fluff]

There is no need to use volatile functions like Indirect for this, it will only slow your workbook down.