Excel help

Messiah Khan · 11 Mar 2011 at 23:12

Anyone got any idea how you reference the last cell that has data in a column? I basically have list that goes down a column and need whatever the bottom value is to be copied into another cell. Any ideas?

36:) · 11 Mar 2011 at 23:46

Hmmm dodgy way of doing it (sorry)

Say you're data is in Column A...

Last cell with data in it would be :
=INDIRECT((ADDRESS(MATCH(999999999999999,A:A,1),COLUMN()-1)))

Put that in any cell in column B...

Just done a quick test with random data, works quite nicely -> doesn't work if you're after text though

Robofish · 12 Mar 2011 at 00:08

With A being the column you want to reference

=offset(A1,counta(A:A)-1,0)

Works with text too!

Messiah Khan · 13 Mar 2011 at 10:26

Thanks guys. That seems to have worked a treat. Another related problem though. Some of the data in those cells comes from formulas. But if no data has been entered in the related cells, the formula displays say 0.00, which obviously is picked up as the bottom value. Is there any way of saying show the bottom value in a column as long as it is over 0.00 (Or any other value that I specify as some formulae defaults are 1.04030 for example.)?

mod1fied · 13 Mar 2011 at 10:45

{=MIN(IF(A1:A5=0,"",A1:A5))}
Entered as an array formula (CTRL + SHIFT + ENTER)
Just change A1:A5 to your range

Only thing is it doesn't appear to work for the whole column, well it didn't for me anyway.