Prev: Cascading Drop-Down Lists
Next: Organizing a column for what's after the @
From: pom15595 on 7 Apr 2010 22:02
If I set up a spreadsheet that includes references of the form =C3 etc, if I
then define a name for cell C3 (eg "cost"), then I have to manually go back
through the spreadsheet to find references to C3 and change them to the name.

Is there a clever way (other than find and replace) to automatically or via
macro etc to go through a spreadsheet at replace all relative references with
the name reference?

Any ideas?
From: ozgrid.com on 7 Apr 2010 22:08
See "Apply Names" in Excel help.


--
Regards
Dave Hawley
www.ozgrid.com
"pom15595" <pom15595(a)discussions.microsoft.com> wrote in message
news:0C0660E1-D968-46CA-9FCE-F5CB1285EECD(a)microsoft.com...
> If I set up a spreadsheet that includes references of the form =C3 etc, if
> I
> then define a name for cell C3 (eg "cost"), then I have to manually go
> back
> through the spreadsheet to find references to C3 and change them to the
> name.
>
> Is there a clever way (other than find and replace) to automatically or
> via
> macro etc to go through a spreadsheet at replace all relative references
> with
> the name reference?
>
> Any ideas?

From: T. Valko on 7 Apr 2010 22:24
This worked for me...

C3 = 97

In G9 I entered a simple link formula =C3
In A15 I entered the formula =C3+3

I then selected cell C3 and gave it the name Cost

With cell C3 still selected
Goto Insert>Name>Apply
Select Cost
OK

The formula in G9 was changed to =Cost
The formula in A15 was changed to =Cost+3

--
Biff
Microsoft Excel MVP


"pom15595" <pom15595(a)discussions.microsoft.com> wrote in message
news:0C0660E1-D968-46CA-9FCE-F5CB1285EECD(a)microsoft.com...
> If I set up a spreadsheet that includes references of the form =C3 etc, if
> I
> then define a name for cell C3 (eg "cost"), then I have to manually go
> back
> through the spreadsheet to find references to C3 and change them to the
> name.
>
> Is there a clever way (other than find and replace) to automatically or
> via
> macro etc to go through a spreadsheet at replace all relative references
> with
> the name reference?
>
> Any ideas?


 | 
Pages: 1
Prev: Cascading Drop-Down Lists
Next: Organizing a column for what's after the @