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 |
Thread Tools | |
Display Modes | |
|
|