Cell Address - print as text?
Cell Address - print as text?
(OP)
I've got a fairly big spreadsheet that covers hundreds of 'cases', for lack of a better word. Each case comprises an array of cells, a few of which are independent variables, modifed for each case. The remainder of the cells in the array are either constant text or numbers, or formulas that refer to the independent variables. All the cases are independent of each other.
To deal with a new case, I copy and paste a case into an empty area, adjust the independent variables, and print out the array for record purposes. Sometimes I need to get back to a specific array, using the information on the printout. For this purpose, I manually put the address of the upper left cell in the upper left cell as text, say 'BE4255.
When I copy that array to make a new one, I have to manually change the cell address in the text. Sometimes I forget, and the printout points to the wrong array.
I'm sorry, all of that seems a long way around, and I'm probably not using the correct jargon, but here is my question: Is there a way to make a cell show its own address?
To deal with a new case, I copy and paste a case into an empty area, adjust the independent variables, and print out the array for record purposes. Sometimes I need to get back to a specific array, using the information on the printout. For this purpose, I manually put the address of the upper left cell in the upper left cell as text, say 'BE4255.
When I copy that array to make a new one, I have to manually change the cell address in the text. Sometimes I forget, and the printout points to the wrong array.
I'm sorry, all of that seems a long way around, and I'm probably not using the correct jargon, but here is my question: Is there a way to make a cell show its own address?
Mike Halloran
NOT speaking for
DeAngelo Marine Exhaust Inc.
Ft. Lauderdale, FL, USA





RE: Cell Address - print as text?
Then, when you print a selection, the row and column headings are automatically printed
TTFN
RE: Cell Address - print as text?
=CELL("col",AB30)&" "&CELL("row",AB30)
where this formula was written in cell AB30
will work. The concatenated blank space is to visually separate the column number from the row number.
There's a way to get the cell to report its own address with letters, although it appears to require a lookup table to correlate the "col" function to the series of letters. This lookup table could be placed on a separate worksheet if desired.
First construct a two column vlookup table with numbers in the left column and the alphabetic lookups in the right. You can generate n many numbers with the "Edit - Fill-Series" command after putting a value of 1 in the upper left cell of the vlookup table, although you won't be needing more than 256 (for a sheet that stops at column 'IV'). You'll have to type the first alphabetic sequence only, as the following cell formula will properly increment the first letter for 26 occurrences while cycling through the second.
=IF(MOD(AG27,26)=0,VLOOKUP(INT(AG27/26)-1,$AG$1:$AH$26,2)&"Z",VLOOKUP(INT(AG27/26),$AG$1:$AH$26,2)&VLOOKUP(MOD(AG27,26),$AG$1:$AH$26,2))
Copy that formula down to your nth row of numbers. That creates your vlookup table. In the above formula, my lookup table was in $AG$1:$AHn.
Then your cell's self-address-seeking formula as written in cell AA31 becomes
=VLOOKUP(CELL("col",AA31),$AG$1:$AH$256,2)&CELL("row",AA31)
Norm
RE: Cell Address - print as text?
And I should have made the following corrections:
"Copy that formula down to your 256th row of numbers. That creates your vlookup table. In the above formula, my lookup table was in $AG$1:$AH256.
Norm
RE: Cell Address - print as text?
CELL
Returns information about the formatting, location, or contents of the upper-left cell in a reference.
Syntax
CELL(info_type,reference)
Info_type is a text value that specifies what type of cell information you want. The following list shows the possible values of info_type and the corresponding results.
Info_type Returns
"address" Reference of the first cell in reference, as text.
RE: Cell Address - print as text?
On trying it again after reading the latest post, it worked as hoped and I can't get it not to unless I delete the cell reference from the formula. Then all of the cell displays do follow the active cell (and they all update whenever the sheet recalculates or is manually recalculated even when the active cell is blank). Go figure.
Oh well, maybe somebody will be able to utilize those other techniques in other situations . . .
Norm
RE: Cell Address - print as text?
Mike Halloran
NOT speaking for
DeAngelo Marine Exhaust Inc.
Ft. Lauderdale, FL, USA