Cell reference

[Don]

Assume that G1 has a range name = test and a value
of "hello". We want to set e5 = +g1 by point and click.
Currently, this will yield "+test" rather than "+g1".
Therefore, if you copy e5 to e6, e6 has a formula
of "+test" rather than "+g2". We want a forumla of "+g2"
in e6. How can we do that with point and click to e5 and
then copying to e6?

 

[Norman Harker]

Hi Don!

Here's a neat trick straight from John Walkenbach's Excel Formulas
2002. I haven't got 2003 yet but it still works in Excel 2003.

Select the cell containing the reference to the name
Tools > Options > Transition
Check "Transition formula entry"
OK
F2
Enter
Tools > Options > Transition
Remove check from "Transition formula entry"
OK

You'll find that the reference to test is now changed to the reference
to the cell. You can now copy down

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Vasant Nanavati]

Or you can just type over the range name in the formula bar with the cell
address <g>.

 

[Norman Harker]

Hi Vasant!

Sorry! I should have pointed out that this was only really useful when
you have more than one name referred to or if you don't want to go off
in a hunt for the address of the name. Using this approach you get all
names converted to the cell addresses without manually editing; it's a
feature that Excel doesn't have.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Vasant Nanavati]

Thanks, Norman ... that's a good trick to remember!

Regards,

Vasant.

 

[Don]

Thanks to both Norman and Vasant for your replies. This
will take care of us. We appreciate the help.

Don