refer to cell based on value

[karlcsr]

Hello. Although I have written extensive worksheets before, my knowledge base is still limited.

I also have an older version of Excel. Don't know if that matters.

I have been wondering how to do this for years and hope you can help me out. I hope I can express myself adequately so you know what I am asking.

I have a repeating formula for each row that builds upon the previous row.
The worksheet may have 500 lines. I may want to analyze 1000 lines of data. I may want to analyze 1500 lines of data.

I would like to know what the value of the last row is -- let's say columnC -- at the top of the worksheet without scrolling down 1000 lines to look.

Of course, the normal way to display the value for columnC 1000 rows down is =zz1000.

Further, I realize I can make a counter in column B to add +1 per row, so the value of cell b1000 would be 1000, the value of b1001 would be 1001.

Further, I realize I could create a formula to determine the max(b1:b5000) to determine how many rows there are.

So now I want to display the valume of Column C, Row number determined by the max(b1:b5000). Since there are no values in, let's say, rows 1001 to 5000, the value would be 1000, demonstrating the last row number.

How do I write a formula at the top of my work sheet to display the value of Column C, Row - (value determined by max(b1:b5000) ????

Does that make sense?? Thank you!!!

[Craig K.]

Do you want to know what the value in the last row is? Or how many rows there are in a column?

[karlcsr]

I would want the value of that cell. Thank you!

[Craig K.]

Try this if you don't have a column header

Please Login or Register  to view this content.

or try this if you do have a column header

Please Login or Register  to view this content.

If you're using a version of excel older than 2007, you need to have start and stop values in the ranges

[Jonmo1]

What type of value is in column C ? Text or Number ? or could it be either?

Try these

For Numeric values in column C
=LOOKUP(9.99999999999999E+307,C:C)

For Text values in column C
=LOOKUP(REPT("z",255),C:C)

If column C could be either text or number
=LOOKUP(2,1/(C:C<>""),C:C)

[karlcsr]

Thank you, Craig. Let me work with this and see if I can get it working.

[karlcsr]

Thanks! Let me try to figure this out. Some of this is a bit new to me.

[karlcsr]

Thank you, Jonmo!

[Jonmo1]

You're welcome.

[karlcsr]

Jonmo,

Yes, I utilized the lookup function for the first time, grasped the concept, and your solution worked perfectly.

I can now display the "final" profit number that is tabulated on the bottom line of my worksheet at the top of the worksheet and will save me lots of time as I plug in different values at the top and then instantly see the results at the top also, versus the very bottom row.

The first value is equal to over a billion, so the lookup function will default to looking for the next lowest value in column C. Since Column C is acting as a counter, the last column will be the largest number and the last column is selected. Then just plug in the corresponding column as the third value in this equation and, bang, I have my profit number! Perfect! Thanks again!

[karlcsr]

I meant to say bottom row, not bottom column above! Rookie mistake!

[karlcsr]

Rookie question. How do I mark a thread "solved"?

[mikeTRON]

Clever solutions Jonmo.

See my signature to mark solved.