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

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/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/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.

0
bigjoec (3)
10/22/2007 6:06:00 PM
excel 39879 articles. 2 followers. Follow

4 Replies
608 Views

Similar Articles

[PageSpeed] 14

bigj...@gmail.com 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.

0
hrlngrv (1990)
10/22/2007 6:32:47 PM
On Oct 22, 1:32 pm, Harlan Grove <hrln...@aol.com> wrote:
> bigj...@gmail.com 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

0
bigjoec (3)
10/22/2007 7:27:12 PM
On Oct 22, 2:27 pm, bigj...@gmail.com wrote:
> On Oct 22, 1:32 pm, Harlan Grove <hrln...@aol.com> wrote:
>
>
>
>
>
> > bigj...@gmail.com 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?

0
bigjoec (3)
10/22/2007 9:58:37 PM
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

<bigjoec@gmail.com> wrote in message 
news:1193090317.487139.298080@y27g2000pre.googlegroups.com...
> On Oct 22, 2:27 pm, bigj...@gmail.com wrote:
>> On Oct 22, 1:32 pm, Harlan Grove <hrln...@aol.com> wrote:
>>
>>
>>
>>
>>
>> > bigj...@gmail.com 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?
> 


0
Charles740 (233)
10/22/2007 11:11:26 PM
Reply:

Similar Artilces:

"De-name" cell references in formulas
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/microsoft.public.excel/browse_frm/thread/4ebb9d1ee0076045/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...