excel search formula
 

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

excel search formula
 

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:

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

excel search formula
 

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

"bcags7" wrote:

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

excel search formula
 

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

"bcags7" a écrit dans le message de news:
...
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

excel search formula
 

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

"Mike H" wrote:

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

"bcags7" wrote:

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

excel search formula
 

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


"bcags7" wrote:

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

excel search formula
 

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


"bcags7" wrote:

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


"bcags7" wrote:

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

excel search formula
 

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.

--

Regards
Roger Govier

"bcags7" wrote in message
...
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


"bcags7" wrote:

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

excel search formula
 

For anything, text or numeric, use this one.

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


Gord Dibben MS Excel MVP

On Wed, 16 Jan 2008 10:08:36 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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.

excel search formula
 

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

--

Regards
Roger Govier

"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
For anything, text or numeric, use this one.

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


Gord Dibben MS Excel MVP

On Wed, 16 Jan 2008 10:08:36 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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.

excel search formula
 

Roger

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


Gord

On Thu, 17 Jan 2008 10:31:07 -0000, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote:

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