Returning a last cell value to another sheet

[hammer2]

Hi all,

Cant remember how I did this all them years ago....

Sheet 2 column H......I want to return the LAST cell value from there to a cell in sheet 1...lets say sheet 1 col b cell 24.

So if the last input in sheet2 col H was in row 24 I want to return that value.

Thanks all.

[JieJenn]

Like this?

[darkyam]

Would this value be text, numbers, or either? For text, use =INDEX(Sheet2!H:H,MATCH(REPT("z",255),Sheet2!H:H)). For numbers, use =Index(Sheet2!H:H,Match(99^99,Sheet2!H:H)). For both, use =Index(Sheet2!H:H,Max(MATCH(REPT("z",255),Sheet2!H:H),Match(99^99,Sheet2!H:H)))

[davesexcel]

Try this, it gets the last numeric value in column H sheet 2

=INDEX(Sheet2!H:H,MATCH(3E+100,Sheet2!H:H,1))

[Haseeb Avarakkan]

Try these,

If it is for number,

=LOOKUP(9E300,Sheet2!H:H)

If it is Text,

=LOOKUP(REPT("z",255),Sheet2!H:H)

[Marcol]

All these years ago?
I'm going to guess you now have 2007 or above, then in say "Sheet 1" B:24

Please Login or Register  to view this content.

If you are still using 2003 then you need to use

Please Login or Register  to view this content.

This is overkill but it should handle Text, Numbers,and Blanks.

[Haseeb Avarakkan]

If you have numbers & texts mixed,

If you are on XL2007 or later,

=INDEX(Sheet2!H:H,MAX(IFERROR(MATCH(9E300,Sheet2!H:H),0),IFERROR(MATCH(REPT("z",255),Sheet2!H:H),0)))

The following method will work all XL versions.

Define 2 named ranges,

Name: LastNumber
Refers to: =MATCH(9E300,Sheet2!$H:$H)

Name: LastText
Refers to: =MATCH(REPT("z",255),Sheet2!$H:$H)

Then use like,

=INDEX(Sheet2!H:H,MAX(IF(ISNUMBER(LastNumber),LastNumber),IF(ISNUMBER(LastText),LastText)))

[hammer2]

Well thank you all very much!!....all ways seem to work. Sorry about not updating my version btw...