Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
gvm
 
Posts: n/a
Default dynamic cell reference within a text string

The documentation of my model includes a text string typed into a cell to the
effect "alter the conversion factor, ie cell D23, to etc etc". My problem is
that as the model develops, the cell containing the conversion factor
changes, ie the reference to D23 becomes wrong. Is there a way of referring
to D23 in the aforementioned text string so that the cell reference changes
in sync with changes to the cell containing conversion factor?

  #2   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

="alter the conversion factor, ie cell "&D23&", to etc etc"

gvm wrote:
The documentation of my model includes a text string typed into a cell to the
effect "alter the conversion factor, ie cell D23, to etc etc". My problem is
that as the model develops, the cell containing the conversion factor
changes, ie the reference to D23 becomes wrong. Is there a way of referring
to D23 in the aforementioned text string so that the cell reference changes
in sync with changes to the cell containing conversion factor?

  #3   Report Post  
gvm
 
Posts: n/a
Default

Thanks Aladin,
but unless I typed it in incorrectly, the effect of your suggestion was to
insert the value contained in D23 which is not what I want. I want the cell
reference itself (D23) inserted, and for this reference to change if the cell
location is changed.

is this possible?
regards,
Greg
  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

Aladin showed you a way to return the value that is in D23.

You could use:

="alter the conversion factor, ie cell " & cell("address",D23) & ", to etc etc"

One thing you may want to do--just to make it easier for you when you come back
to it in a few months--is to give that cell a nice name.

Select D23 (or whereever it is)
insert|Name|Define
Names in Workbook
ConvFact

="alter the conversion factor, ie cell " & cell("address",convfact) & ", to..."

=======
If you want to see D23 instead of $D$23, use this

= "xxx" & SUBSTITUTE(CELL("address",convfact),"$","") & "yyyy"



gvm wrote:

Thanks Aladin,
but unless I typed it in incorrectly, the effect of your suggestion was to
insert the value contained in D23 which is not what I want. I want the cell
reference itself (D23) inserted, and for this reference to change if the cell
location is changed.

is this possible?
regards,
Greg


--

Dave Peterson
  #5   Report Post  
gvm
 
Posts: n/a
Default

That is exactly what I needed and more, thank you Dave


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how do I convert text string into a cell reference Dave Davis Excel Discussion (Misc queries) 4 May 18th 23 11:48 AM
Maintaining cell reference after sorting GRITS Excel Discussion (Misc queries) 2 April 30th 23 07:42 PM
Formulas dealing with text data Bagia Excel Worksheet Functions 6 June 20th 05 10:29 PM
dynamic external cell reference bg.itdept Excel Worksheet Functions 4 February 19th 05 04:15 AM
How to make a cell return the formatted value in a text string (i. n.almeida Excel Worksheet Functions 3 February 2nd 05 02:59 PM


All times are GMT +1. The time now is 08:13 PM.

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

About Us

"It's about Microsoft Excel"