Referencing the last input of a column

[Ody]

Hello all,

I'm stuck trying to figure out how to dynamically reference the last input of a column on my roll up sheet (i.e. there are multiple sheets). I know the answer is simple but it escapes me presently.

Thanks for the help

 

[Jonmo1]

Is the data numbers or text, or mixture of both?

If Numbers, use
=LOOKUP(9.99999999999999E+307,A:A)

If it's Text, use
=LOOKUP(REPT("Z",255),A:A)

hope that helps.

 

[Ody]

Thanks for the quick response. The data is numerical.

I tried your lookup solution with out any luck. I used the following: =lookup(9.999999999999999,Sheet1!G:G) and received the #N/A error.

Suggestions?

 

[Jonmo1]

That means the numbers in column G are not really numbers
They are "Numbers Stored As Text"

Try the second one with REPT("Z",255)


Or convert the values in column G to real numbers..
Copy any blank cell, highlight column G, Right Click - Paste Special -Values - Add

 

[Ody]

Thanks for the update.

The numbers aren't stored as text but in trying to get your solution to work I considered trying an index formula and that solved my dilemma.

Thanks for your help!

 

[Jonmo1]

Care to share the formula you used?

 

[Ody]

Sure! I used the following:

=INDEX(A:A,COUNT(A:A))

Just edit for sheet names(and appropriate columns) as necessary.

 

[Jonmo1]

Wait, I see the problem...

You said

I used the following: =lookup(9.999999999999999,Sheet1!G:G)

However, my suggestion was

=LOOKUP(9.99999999999999E+307,A:A)


The difference between
9.99999999999999E+307
and
9.999999999999999

is enormous.

 

[Ody]

You know what, I forgot the "E" in 9.9999999+307 when I used the formula. After making that adjustment it works, lol. So i guess that's 2 solutions!

Thanks again!

 

[Jonmo1]

Yes, 2 possible solutions.

However, the lookup will be more robust.
Indexing with Count will be eroneous if there are blanks in the range..


Hope that helps..

Excel Workbook
	A	B	C	D
1	Header		Lookup	Index/count
2	420		377	443
3	435
4
5
6	443
7	778
8
9	175
10	377
Sheet1