Replacement for using Indirect (Volatile)

Hi,

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

Originally Posted by rocky1

Hi,

What can I use to replace the portion in red because it is volatile?

=IF(A3="N","",RANK(B3,$B$2:INDIRECT("$B$17"),0)+COUNTIF($B$2:B3,B3)-1)

Try this:

Please Login or Register  to view this content.

Is that something you can work with?

Thanks for the help Ron.

I am actually looking for something that could be a little more dynamic. For this example I just used B17, but I'm not sure where the end of the column is. I could write INDIRECT("$B$2000") or even further, but of course as soon as I do that somebody will have more rows. What can I use that will give me the address of the last cell?

Originally Posted by rocky1

Thanks for the help Ron.

I am actually looking for something that could be a little more dynamic. For this example I just used B17, but I'm not sure where the end of the column is. I could write INDIRECT("$B$2000") or even further, but of course as soon as I do that somebody will have more rows. What can I use that will give me the address of the last cell?

Ah...now that's a different question.

And...since you're using Excel 2007...
If there won't be any blanks within the number range,
I think this formula will work for you:

Please Login or Register  to view this content.

Is that something you can work with?
If not...we'll probably have to use a dynamic named range.

Originally Posted by rocky1

Hi Ron,

I must be messing this one up, but I am getting #N/A and #VALUE for some cells. I've attached a sample of what I am trying. Is there anyway to incorporate the formula in H6? If we used a dynamic range isn't that also volatile?

I edited your posted workbook to include the following changes:

1) I added a Named Range
Name: LastNumCell
Refers to: =INDEX($B:$B,MAX(ISNUMBER($B:$B)*ROW($B:$B)))

2) New formula:

Please Login or Register  to view this content.

copy that formula down as far as you need.

Does that help?