"De-name" cell references in formulas - Excel 2003

[bigjoec]

Hello,
I'm working in Excel 2003 SP2, and I'm trying to replace all
references to named ranges used within my formulas within a workbook
with the underlying absolute cell references. I found a solution in
the archives of this newsgroup (
http://groups.google.com/group/micr...d1ee0076045/a0d6406155d03ccc#a0d6406155d03ccc
) , but I can't get it to work (neither the manual version nor the
macro version).

My only guess is that the behavior has of TransitionFormEntry has
changed in Excel 2003. If so, is anyone aware of any other solutions.
I found another solution as well ( http://www.vbaexpress.com/kb/getarticle.php?kb_id=675
) but it uses a textual find-and-replace on the names of the named
ranges, and that feels a little scary to me - I'd rather have
something I'm certain won't mess up the formulas.

Also, and this should probably be a separate request but I'll ask
anyway - does anyone have a method for removing "redundant" worksheet
references within all formulas in a workbook? E.g., if on the
worksheet named 'ThisSheet' I have the formula
B1 = 'ThisSheet'A1 * 2,
then I'm looking to remove the redundant 'ThisSheet' name and just
have the formula be
B1 = A1 * 2
Again, I'd rather not use textual find-and-replace, but I may be stuck
for this one.

 

[Harlan Grove]

(e-mail address removed) wrote...

I'm working in Excel 2003 SP2, and I'm trying to replace all
references to named ranges used within my formulas within a
workbook with the underlying absolute cell references. I found a
solution in the archives of this newsgroup

[replacing truncated url] http://tinyurl.com/ywotsv

but I can't get it to work (neither the manual version nor the
macro version).

....

ALWAYS be DETAILED AND PRECISE about how something doesn't work!
Doesn't work how? Does your PC blow up when you try it? Does Excel
crash? Do your formulas change but not exactly as you want them to? Do
your formulas remain unchanged?

The manual and macro methods in the thread you cite DO replace defined
names with corresponding cell references IN EXCEL 2003, but those cell
references are relative rather than absolute. Nothing in the cited
thread stated that defined names would be replaced with absolute
references.

I found another solution as well >http://www.vbaexpress.com/kb/getarticle.php?kb_id=675
but it uses a textual find-and-replace on the names of the named
ranges, and that feels a little scary to me - I'd rather have
something I'm certain won't mess up the formulas.

....

The only workable solution given your specs is find-and-replace. If
that makes you nervous, reconsider why you're trying to do this.

Also, and this should probably be a separate request but I'll ask
anyway - does anyone have a method for removing "redundant"
worksheet references within all formulas in a workbook? E.g., if on
the worksheet named 'ThisSheet' I have the formula
B1 = 'ThisSheet'A1 * 2,
then I'm looking to remove the redundant 'ThisSheet' name and just
have the formula be
B1 = A1 * 2
Again, I'd rather not use textual find-and-replace, but I may be
stuck for this one.

You're stuck with find-and-replace for both.

 

[bigjoec]

(e-mail address removed) wrote...

I'm working in Excel 2003 SP2, and I'm trying to replace all
references to named ranges used within my formulas within a
workbook with the underlying absolute cell references. I found a
solution in the archives of this newsgroup

[replacing truncated url] http://tinyurl.com/ywotsv

but I can't get it to work (neither the manual version nor the
macro version).

...

ALWAYS be DETAILED AND PRECISE about how something doesn't work!
Doesn't work how? Does your PC blow up when you try it? Does Excel
crash? Do your formulas change but not exactly as you want them to? Do
your formulas remain unchanged?

The manual and macro methods in the thread you cite DO replace defined
names with corresponding cell references IN EXCEL 2003, but those cell
references are relative rather than absolute. Nothing in the cited
thread stated that defined names would be replaced with absolute
references.

I found another solution as well >http://www.vbaexpress.com/kb/getarticle.php?kb_id=675
but it uses a textual find-and-replace on the names of the named
ranges, and that feels a little scary to me - I'd rather have
something I'm certain won't mess up the formulas.

...

The only workable solution given your specs is find-and-replace. If
that makes you nervous, reconsider why you're trying to do this.

Also, and this should probably be a separate request but I'll ask
anyway - does anyone have a method for removing "redundant"
worksheet references within all formulas in a workbook? E.g., if on
the worksheet named 'ThisSheet' I have the formula
B1 = 'ThisSheet'A1 * 2,
then I'm looking to remove the redundant 'ThisSheet' name and just
have the formula be
B1 = A1 * 2
Again, I'd rather not use textual find-and-replace, but I may be
stuck for this one.

You're stuck with find-and-replace for both.

The problem I'm having with the linked solution is that performing it
does nothing (as far as I can tell) to my spreadsheet.

Here's a blow-by-blow of the "manual" version of the solution:

1. Click on cell AV8, and see the following displayed:
=COB_AfterBurn_C1
2. Go to Tools->Options->Transitions, and check the checkbox next to
"Transition Formula Evaluation". Click "OK"
3. Click on cell AV8, hit F2 to enter the formula entry box, and hit
ENTER
4. <just because I'm curious, click on cell AV8 again, and note that
it still displays the formula: =COB_AfterBurn_C1 >
5. Go to Tools->Options->Transitions, and clear the checkbox next to
"Transition Formula Evaluation". Click "OK"
6. Again click on cell AV8, and it still diplays = COB_AfterBurn_C1

Similarly, running the macro had no impact on any formulas within the
spreadsheet (as far as I can tell).

So I'm guessing by your reply that this wasn't a behavior change in
Excel 2003 - i.e., that you've been able to verify that this procedure
works for you in Excel 2003. So I'm at a loss as to why it's not
working for me in this spreadsheet. Does anyone have any thoughts?

[PS - My mentioning of "absolute references" was a poor description on
my part. For this purpose, I'd be happy with either relative or
absolute, as long as I can get the Defined name translated back to the
A1C1 reference.]

Thanks,
Joe

 

[bigjoec]

(e-mail address removed) wrote...

[replacing truncated url] http://tinyurl.com/ywotsv

but I can't get it to work (neither the manual version nor the
macro version).

...

ALWAYS be DETAILED AND PRECISE about how something doesn't work!
Doesn't work how? Does your PC blow up when you try it? Does Excel
crash? Do your formulas change but not exactly as you want them to? Do
your formulas remain unchanged?

The manual and macro methods in the thread you cite DO replace defined
names with corresponding cell references IN EXCEL 2003, but those cell
references are relative rather than absolute. Nothing in the cited
thread stated that defined names would be replaced with absolute
references.

The only workable solution given your specs is find-and-replace. If
that makes you nervous, reconsider why you're trying to do this.

You're stuck with find-and-replace for both.

The problem I'm having with the linked solution is that performing it
does nothing (as far as I can tell) to my spreadsheet.

Here's a blow-by-blow of the "manual" version of the solution:

1. Click on cell AV8, and see the following displayed:
=COB_AfterBurn_C1
2. Go to Tools->Options->Transitions, and check the checkbox next to
"Transition Formula Evaluation". Click "OK"
3. Click on cell AV8, hit F2 to enter the formula entry box, and hit
ENTER
4. <just because I'm curious, click on cell AV8 again, and note that
it still displays the formula: =COB_AfterBurn_C1 >
5. Go to Tools->Options->Transitions, and clear the checkbox next to
"Transition Formula Evaluation". Click "OK"
6. Again click on cell AV8, and it still diplays = COB_AfterBurn_C1

Similarly, running the macro had no impact on any formulas within the
spreadsheet (as far as I can tell).

So I'm guessing by your reply that this wasn't a behavior change in
Excel 2003 - i.e., that you've been able to verify that this procedure
works for you in Excel 2003. So I'm at a loss as to why it's not
working for me in this spreadsheet. Does anyone have any thoughts?

[PS - My mentioning of "absolute references" was a poor description on
my part. For this purpose, I'd be happy with either relative or
absolute, as long as I can get the Defined name translated back to the
A1C1 reference.]

Thanks,
Joe- Hide quoted text -

- Show quoted text -

Okay, I did a bit more testing and discovered two things:

1. I was testing this manually with the wrong checkbox. Should be
using "transition formula entry" not "evaluation". Duh.
2. This procedure only works for references to cells that reside on
the same sheet as the formula. That's why the macro version didn't
work for me, because most references were to cells named on other
sheets.

Obviously I solved issue one above. Does anyone have thoughts for
issue 2?

 

[Charles Williams]

I have not found a good solution yet. It gets even more difficult when you
consider things like names being referenced in names etc (and INDIRECT).
I think that probably a reasonable general solution involves parsing out
defined name references from all formulae wherver they are used (which is
many different places).
This is on the list as a future Name Manager improvement.

regards
Charles
______________________
Decision Models
FastExcel 2.3 now available
Name Manager 4.0 now available
www.DecisionModels.com

(e-mail address removed) wrote...
I'm working in Excel 2003 SP2, and I'm trying to replace all
references to named ranges used within my formulas within a
workbook with the underlying absolute cell references. I found a
solution in the archives of this newsgroup

[replacing truncated url] http://tinyurl.com/ywotsv

but I can't get it to work (neither the manual version nor the
macro version).

ALWAYS be DETAILED AND PRECISE about how something doesn't work!
Doesn't work how? Does your PC blow up when you try it? Does Excel
crash? Do your formulas change but not exactly as you want them to? Do
your formulas remain unchanged?

The manual and macro methods in the thread you cite DO replace defined
names with corresponding cell references IN EXCEL 2003, but those cell
references are relative rather than absolute. Nothing in the cited
thread stated that defined names would be replaced with absolute
references.

I found another solution as well
http://www.vbaexpress.com/kb/getarticle.php?kb_id=675
but it uses a textual find-and-replace on the names of the named
ranges, and that feels a little scary to me - I'd rather have
something I'm certain won't mess up the formulas.

The only workable solution given your specs is find-and-replace. If
that makes you nervous, reconsider why you're trying to do this.

Also, and this should probably be a separate request but I'll ask
anyway - does anyone have a method for removing "redundant"
worksheet references within all formulas in a workbook? E.g., if on
the worksheet named 'ThisSheet' I have the formula
B1 = 'ThisSheet'A1 * 2,
then I'm looking to remove the redundant 'ThisSheet' name and just
have the formula be
B1 = A1 * 2
Again, I'd rather not use textual find-and-replace, but I may be
stuck for this one.

You're stuck with find-and-replace for both.

The problem I'm having with the linked solution is that performing it
does nothing (as far as I can tell) to my spreadsheet.

Here's a blow-by-blow of the "manual" version of the solution:

1. Click on cell AV8, and see the following displayed:
=COB_AfterBurn_C1
2. Go to Tools->Options->Transitions, and check the checkbox next to
"Transition Formula Evaluation". Click "OK"
3. Click on cell AV8, hit F2 to enter the formula entry box, and hit
ENTER
4. <just because I'm curious, click on cell AV8 again, and note that
it still displays the formula: =COB_AfterBurn_C1 >
5. Go to Tools->Options->Transitions, and clear the checkbox next to
"Transition Formula Evaluation". Click "OK"
6. Again click on cell AV8, and it still diplays = COB_AfterBurn_C1

Similarly, running the macro had no impact on any formulas within the
spreadsheet (as far as I can tell).

So I'm guessing by your reply that this wasn't a behavior change in
Excel 2003 - i.e., that you've been able to verify that this procedure
works for you in Excel 2003. So I'm at a loss as to why it's not
working for me in this spreadsheet. Does anyone have any thoughts?

[PS - My mentioning of "absolute references" was a poor description on
my part. For this purpose, I'd be happy with either relative or
absolute, as long as I can get the Defined name translated back to the
A1C1 reference.]

Thanks,
Joe- Hide quoted text -

- Show quoted text -

Okay, I did a bit more testing and discovered two things:

1. I was testing this manually with the wrong checkbox. Should be
using "transition formula entry" not "evaluation". Duh.
2. This procedure only works for references to cells that reside on
the same sheet as the formula. That's why the macro version didn't
work for me, because most references were to cells named on other
sheets.

Obviously I solved issue one above. Does anyone have thoughts for
issue 2?