excel search formula

[bcags7]

I'm trying to create a formula that searches a column for the last cell that
contains data, and then returns that value to a cell in another worksheet.
Could anyone help me out with this? Thanks

 

[Stefi]

This works only if there are no blank cells between the first and last cell
of the column Sheet1!B. Enter this formula toa cell in another sheet:
=INDIRECT("Sheet1!B"&COUNTA(B:B))

If there are blank cells between them then - as far as I know - you'll need
an UDF.

Regards,
Stefi

„bcags7†ezt írta:

 

[Mike H]

Hi,

Try this array to return the last value (text or numeric) from column A

=INDIRECT("A"&MAX(IF(NOT(ISBLANK(A1:A65535)),ROW(1:100))))

Note it's an array so enter with Ctrl+Shift+Enter

Mike

 

[papou]

Hi
On worksheet Sheet2 for instance, formula to get value from last row in
column A of worksheet Sheet1:
=OFFSET(Sheet1!A1,,,COUNTA(Sheet1!A:A))

HTH
Cordially
Pascal

 

[Mike H]

OOPS,

I did of course mean

=INDIRECT("A"&MAX(IF(NOT(ISBLANK(A1:A65535)),ROW(1:65535))))

This copes with blanks in the range.

Mike

 

[bcags7]

Thanks for the input but these formulas are not working. I'm getting a return
value of #N/A. I've found a another formula in a previous post that seems to
work...=LOOKUP(9.99999999999999E+307,A2:K2).

Just a couple questions though...what is the 9.999999E+307? And is it
possible to search a whole column and not just the range, such as A2:K2. In
my worksheet I'm trying to search column H for the last numerical value.
Thanks again

 

[Mike H]

All of the formula given to you 'work' if applied correctly.

The formula you have found can be converted to search an entire row by
changing it to this

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

Mike

 

[Roger Govier]

Hi

what is the 9.999999E+307?

It is just a large number (the largest that can be entered into Excel) or
one that would be unlikely to be found on your sheet.
For most purposes 99^99 is large enough, and many people use this in their
formulae instead. It is easier to type and to remember<bg>
So, =LOOKUP(99^99,H:H)
will find the last numeric value in column H

If it is Text that you are looking for, then
=LOOKUP(REPT("Z",255)H:H)
will find the last test item.

REPT("Z",255) is a string of 255 Z's, i.e. something that is unlikely to be
found in your spreadsheet.

 

[Gord Dibben]

For anything, text or numeric, use this one.

'=LOOKUP(2,1/(A1:A65535<>""),A1:A65535)


Gord Dibben MS Excel MVP

 

[Roger Govier]

I had completely forgotten that that formula did both.
Thanks for reminding me Gord!

 

[Gord Dibben]

Roger

I like to have all three available to suit the situation.


Gord