Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Placing Col & Row identifiers into a cell as text
How do I get the Col letter and Row number of a cell (without the page name)
to appear as text in another cell? Using the =CELL("address",xxx) and clicking on another page for the xxx cell places the entire [workbook name]sheetname!$col$row in the cell. I only want the col letter and the row #. How do I get only these? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Placing Col & Row identifiers into a cell as text
Here's one way:
=RIGHT(CELL("address",Sheet2!xxx),LEN(CELL("addres s",Sheet2!xxx))- FIND("!",CELL("address",Sheet2!xxx))) where xxx is the cell reference. If you do not want $ symbols, you can use SUBSTITUTE around the formula to change them to blanks. Hope this helps. Pete On May 28, 12:54*am, John wrote: How do I get the Col letter and Row number of a cell (without the page name) to appear as text in another cell? Using the =CELL("address",xxx) and clicking on another page for the xxx cell places the entire [workbook name]sheetname!$col$row in the cell. *I only want the col letter and the row #. *How do I get only these? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Placing Col & Row identifiers into a cell as text
Hi John,
Try this: =RIGHT(CELL("address",Sheet2!A1),4) This gives the last 4 characters, which eliminates the unwanted stuff. Regards - Dave |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Placing Col & Row identifiers into a cell as text
Oops,
UK Pete's answer is better. Mine doesn't work above row 9 or column Z Regards - Dave |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Placing word into a seperate cell | Excel Discussion (Misc queries) | |||
Difficulty placing active email / hyperlinks with normal text in c | Excel Discussion (Misc queries) | |||
How do I change Excel column cell identifiers back to letters? | Excel Discussion (Misc queries) | |||
Logic for Placing Multiple field Returns in a Cell | Excel Discussion (Misc queries) | |||
Can I change the format of one cell by placing a tick in another? | Excel Discussion (Misc queries) |