A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Convert a Cell Reference to Text



 
 
Thread Tools Display Modes
  #1  
Old September 22nd, 2003, 03:24 AM
Chuck Buker
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 03:47 AM
Vasant Nanavati
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 08:23 AM
Harlan Grove
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 03:37 PM
Vasant Nanavati
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 03:44 PM
Chuck Buker
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 04:42 PM
Vasant Nanavati
external usenet poster
 
Posts: n/a
Default 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  
Old September 22nd, 2003, 05:04 PM
Chuck Buker
external usenet poster
 
Posts: n/a
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:20 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.