If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert a Cell Reference to Text
Cell A2 contains the text "a100". Cells A5 through F15 contain indirect and offset functions that use the text in A2 to cause cells A5 through F15 to refer to cells near cell A100. For example, cell A5 contains the formula: =offset(indirect($a$2),2,4). This causes cell A5 to display the contents of cell E102. I can instantly change what is displayed in each of the 66 cells A5 through F15 simply by changing the text in cell A2. Unfortunately, using this example, if I 'move' cell A100 or insert some number of rows into the spreadsheet above cell A100, cell A5 continues to display the contents of cell E102 even though the original contents of cell A100 and, if I have moved E102 or inserted rows above A100, the original contents of cell E102 are now somewhere else. This is because the text "a100" in cell A2 did not change when I moved cell A100. If cell A2, instead of containing text, contained a cell reference (e.g., +a100), and another cell, say A3, contained an Excel function that converted the cell reference in A2 to text (e.g., from +a100 to "a100"), then I could point my offset/indirect function to cell A3 [e.g., =offset(indirect($a$3),2,4)], and the reference would effectively follow cell A100 as it was moved around the spreadsheet. The function I am looking for is alomst the opposite of the 'indirect' function - instead of converting text to a cell reference, it converts a cell reference to text. How can I accomplish this? Thanks, Chuck |
#2
|
|||
|
|||
Convert a Cell Reference to Text
Hi Chuck:
In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) Regards, Vasant. "Chuck Buker" wrote in message ... Cell A2 contains the text "a100". Cells A5 through F15 contain indirect and offset functions that use the text in A2 to cause cells A5 through F15 to refer to cells near cell A100. For example, cell A5 contains the formula: =offset(indirect($a$2),2,4). This causes cell A5 to display the contents of cell E102. I can instantly change what is displayed in each of the 66 cells A5 through F15 simply by changing the text in cell A2. Unfortunately, using this example, if I 'move' cell A100 or insert some number of rows into the spreadsheet above cell A100, cell A5 continues to display the contents of cell E102 even though the original contents of cell A100 and, if I have moved E102 or inserted rows above A100, the original contents of cell E102 are now somewhere else. This is because the text "a100" in cell A2 did not change when I moved cell A100. If cell A2, instead of containing text, contained a cell reference (e.g., +a100), and another cell, say A3, contained an Excel function that converted the cell reference in A2 to text (e.g., from +a100 to "a100"), then I could point my offset/indirect function to cell A3 [e.g., =offset(indirect($a$3),2,4)], and the reference would effectively follow cell A100 as it was moved around the spreadsheet. The function I am looking for is alomst the opposite of the 'indirect' function - instead of converting text to a cell reference, it converts a cell reference to text. How can I accomplish this? Thanks, Chuck |
#3
|
|||
|
|||
Convert a Cell Reference to Text
"Vasant Nanavati" wrote...
In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) .... Or =CELL("Address",A100) |
#4
|
|||
|
|||
Convert a Cell Reference to Text
Yes, indeed ... I always forget about all the CELL variants g.
"Harlan Grove" wrote in message ... "Vasant Nanavati" wrote... In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) ... Or =CELL("Address",A100) |
#5
|
|||
|
|||
Convert a Cell Reference to Text
Thanks very much. Both will do the job.
Now my next question ... since I sent that post last night and stupidly included my email address, I have received more than 30 junk emails, all of which contained various worms and viruses. Is this common, will it dissipate, or will I have to get a spam filter or change my email address to make it stop? Chuck -----Original Message----- "Vasant Nanavati" wrote... In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) .... Or =CELL("Address",A100) . |
#6
|
|||
|
|||
Convert a Cell Reference to Text
Hi Chuck:
Welcome to the club! This is a new but unfortunate phenomenon. Many of us here have been getting inundated with virus-containing emails since last week. Some of us are getting 200-300 a day. I'm hoping that this will dissipate soon as I would hate to change my email address. One of the MVPs has reported good results using SpamAssassin; I have no experience with it personally. Good luck! Regards, Vasant. "Chuck Buker" wrote in message ... Thanks very much. Both will do the job. Now my next question ... since I sent that post last night and stupidly included my email address, I have received more than 30 junk emails, all of which contained various worms and viruses. Is this common, will it dissipate, or will I have to get a spam filter or change my email address to make it stop? Chuck -----Original Message----- "Vasant Nanavati" wrote... In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) .... Or =CELL("Address",A100) . |
#7
|
|||
|
|||
Convert a Cell Reference to Text
The Cell function works whether the cell being referred to
is in the same worksheet or in another worksheet in the same spreadsheet. In contrast, the Address function gives only the address without the worksheet identifier and therefore does not accomplish the intended purpose if the cell being referrred to is in another worksheet. Thanks again ... and let me know if you think I hosed myself by putting my email address in the original post. I have now received 35 of the "Microsoft latest security patch" virus infected emails. Chuck -----Original Message----- Yes, indeed ... I always forget about all the CELL variants g. "Harlan Grove" wrote in message ... "Vasant Nanavati" wrote... In cell A2, instead of: A100 use: =ADDRESS(ROW(A100),COLUMN(A100)) ... Or =CELL("Address",A100) . |
Thread Tools | |
Display Modes | |
|
|