Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i am in search of Formula / Function in EXCEL-2003 | Excel Discussion (Misc queries) | |||
Excel 2002: Can Vlookup formula search for data with two reference | Excel Discussion (Misc queries) | |||
Getting Excel whilst using the sumif formula to search for part of a word | Excel Discussion (Misc queries) | |||
can i make a formula to search keywords in a excel workbook? | Excel Worksheet Functions | |||
Excel - Formula Query: Search for and Return Value | Excel Worksheet Functions |