Cell Address - print as text?

[MikeHalloran]

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?



Mike Halloran
NOT speaking for
DeAngelo Marine Exhaust Inc.
Ft. Lauderdale, FL, USA

 

[IRstuff]

??? Why not Page Setup with "Row and Column Headings" checked?

Then, when you print a selection, the row and column headings are automatically printed

TTFN

 

[NormPeterson]

If you don't mind substituting numbers for the lettered column identification:

=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

 

[NormPeterson]

To clarify (since I hit the 'submit' button instead of 'preview'), the lookup table formula for generating the letter portions of the address starts in the 27th row and gets copied to the 256th row.

=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))

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

 

[jghrist]

Why not simply CELL("address",address of upper-left corner)?

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.

 

[NormPeterson]

Why not simply CELL("address",address of upper-left corner)?

That's what I first tried, and when I copied and pasted the formula around the sheet all of those formulas changed to report the address of the newest cell. I have no idea why that happened, hence the rather cumbersome work-around.

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

 

[MikeHalloran]

Thanks, guys.



Mike Halloran
NOT speaking for
DeAngelo Marine Exhaust Inc.
Ft. Lauderdale, FL, USA