Change a Range Based on another Cell

[dianaschar]

So I have a changing list of of data. I want to be able to change this formula dynamically..

=COUNTBLANK(Data!J2:J2798)-V5

I want the end of the range to change based on a value I have calculated in cell V3.

Any thoughts?

So the formula I would essentially want is - =CountBlank(Data!J2:J(V3))-V5

Is this possible?

[FDibbins]

Ho and welcome to the forum

you can do this 1 of 2 ways.

change your formula to =COUNTBLANK(Data!J:J)-V5

or use the indirect function inside your formula

something like...
=COUNTBLANK(INDIRECT("Data!J2:J"&V3))

[Pete_UK]

Yes, it is possible - try this:

=COUNTBLANK(INDIRECT("'Data'!J2:J"&V3))-V5

Hope this helps.

Pete

[dianaschar]

Thank you!

Pete this totally worked! You're awesome.