Cell reference

  • Thread starter Thread starter Don
  • Start date Start date
D

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?
 
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.
 
Or you can just type over the range name in the formula bar with the cell
address <g>.
 
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.
 
Thanks to both Norman and Vasant for your replies. This
will take care of us. We appreciate the help.

Don
 
Back
Top