Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the Cell ID
Hi,
I have got an Excel sheet in which text and numbers are there. 1) I want to get the ID of the cell with specified text. Is there any function for doing that? 2) How can I find the last row with data in the same sheet. Thanks, Shaiju |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the Cell ID
Hi
Assuming your column containing the text is column A , and the text you were looking for is Roger, then =ADDRESS(MATCH("Roger",A:A,0),1) The 1 at the end refers to column A. adjust for other columns. Finding the last row with data, will depend upon whether tit is Numeric or Text. For Numeric =LOOKUP(99^99,A:A) For Text( =LOOKUP(REPT("Z",255),A:A) In each case you are choosing some arbitrary value that is unlikely to occur in the column. I have chosen 99 to the power of 99. Some people recommend using 9.99999999999999E+307 which is the largest number that can be written in an Excel cell, but I find that cumbersome to remember and type. 99^99 has always more than covered the values for me. -- Regards Roger Govier wrote in message ... Hi, I have got an Excel sheet in which text and numbers are there. 1) I want to get the ID of the cell with specified text. Is there any function for doing that? 2) How can I find the last row with data in the same sheet. Thanks, Shaiju |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Getting the Cell ID
On Oct 28, 5:02*pm, "Roger Govier"
<roger@technology4unospamdotcodotuk wrote: Hi Assuming your column containing the text is column A , and the text you were looking for is Roger, then =ADDRESS(MATCH("Roger",A:A,0),1) The 1 at the end refers to column A. adjust for other columns. Finding the last row with data, will depend upon whether tit is Numeric or Text. For Numeric =LOOKUP(99^99,A:A) For Text( =LOOKUP(REPT("Z",255),A:A) In each case you are choosing some arbitrary value that is unlikely to occur in the column. I have chosen 99 to the power of 99. Some people recommend using 9.99999999999999E+307 which is the largest number that can be written in an Excel cell, but I find that cumbersome to remember and type. 99^99 has always more than covered the values for me. -- Regards Roger Govier wrote in message ... Hi, I have got an Excel sheet in which text and numbers are there. 1) I want to get the ID of the cell with specified text. Is there any function for doing that? 2) How can I find the last row with data in the same sheet. Thanks, Shaiju- Hide quoted text - - Show quoted text - Hi Roger, Thank you very much. Shaiju. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
Inputting cell value from source cell based on value in adjacent cell. | Excel Discussion (Misc queries) | |||
cell data not validated if navigating cell to cell with mouse | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Cell Formula reference to cell Based On third Cell Content | Excel Discussion (Misc queries) |