dynamic range, counta and offset combination

hi! I am trying to set a range (for a lookup). I have the start cell, but the last cell will change as records are added/removed. I think I need to offset the last cell by the number of rows that I commence my count from.

Here is what I have so far>

="$A$9:$A$"&COUNTA(INDIRECT("$O$9:$O$10000")) >>(not sure why, but if I remove INDIRECT, the formulae does not work. I need the INDIRECT part for a larger function however).

This function will count values in column O to make the range (in column A), however the range will be 8 records short becuse I need to start the count from row 9. How can I fix this without adding "+8" into my range calc?

Thank you so much!

Thanks MarvinP, But I neglected to mention that I cannot name the range (it is a constraint I have). I need the formulae to create the range on the fly, but not name it. I have a matrix, 100X 100, with each cell refering to data throughout a very large spreadsheet. Naming ranges will create thousands of names. I am not allowed to add even a single name. How can we do this?

So, I have the first cell. (e.g. A9)
Next I need to jump to the last cell (there are blank cells along the way)
Then I need excel to return the cell coordinates and plug into a larger formula. (e.g. A5000)
The result will be A9:A5000, which I will plug into another formulae as a string.

I appreciate any advice.

Thanks, chomo

Hi Jacc. Please see response to Marvin. I cannot name the ranges.

Hi Tony, Thanks for the tip. That did not work work. I may have input incorrectly. Please see the attached sheet with more detail of my problem. This should be much easier to grasp now. Please let me know how you go.

This doesn't work because it's a flawed logic to the dynamic range.
=SUMPRODUCT(--(INDIRECT($B$3&"!$D$9:$D$"&COUNTA(INDIRECT($B$3&"!$D$9:$D$10000")))=""))

This part
COUNTA(INDIRECT($B$3&"!$D$9:$D$10000"))
Is counting non blanks in D9:D10000
In your given sheet, that number happens to be 46
But given that your ragne begins in Row 9, AND since those blanks are not contiguous, and spread out through the whole range, then 46 doesn't actually translate to a useable Row# for your range.

So with counta returning 46, your formula is translated to
=SUMPRODUCT(--(INDIRECT($B$3&"!$D$9:$D$46")=""))
D9:D46 is not the actual range.


Tony's version will work better.
But his post appears to be just a general suggestion, and not written specifically for your ranges and formula.
in the book you posted, you didn't really apply it to your sumproduct formula.
Try
=SUMPRODUCT(--($D$9:INDEX($D:$D,MATCH(1E+100,$D:$D))=""))

You can then apply the indrect function as needed.

Hi Jonmo, thanks for the guidance. The quality of this forum is tip-top. You found it! Awesome.

I am now having difficulty joining it with the indirect function>

=SUMPRODUCT(--(INDIRECT($B$3&"!$D$9:INDEX($D:$D,MATCH(1E+101,$D:$D)=""")))

(where B3 is the name of the worksheet) All I get is #REF returned.

Any ideas?

Thanks!
chomo
tokio

WOW Tony. you r the man! Although MASSIVE, this formulae worked! Thanks so much. Any short hand for this baby!?

Cheers Tony! and thank you to everyone else who have helped me along the way!