×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Cell Address - print as text?

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?

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

RE: Cell Address - print as text?

??? 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

RE: Cell Address - print as text?

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

RE: Cell Address - print as text?

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.

Quote:

=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

RE: Cell Address - print as text?

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.

RE: Cell Address - print as text?

Quote:

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

RE: Cell Address - print as text?

(OP)
Thanks, guys.

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources