Named ranges: don't want absolute references
 

Here's my dilemma, and it's a large one as my company utilizes a large
model where this is an issue. When named ranges are references, they
are - by default - absolute references. This is a problem because
named ranges are very good identifiers to decode a formula (and our
model uses very long, complicated formulas).

Say I have a table with 50 rows, one for each state. The other columns
are RATES, MODS and SURCHG, with range names all around. Elsewhere in
the file, I have a cell with this formula (for CA, e.g, and I can type
it in just like this): CA_RATE * CA_MOD+CA_SURCHG.

Problem is, say Delaware is right below California in my source table.
I would like to simply copy this formula and have it read: DE_RATE *
DE_MOD+DE_SURCHG. But, since my range name references are absolute, I
can't copy down and get what I want. I have to manually go in and
change all the "CA"s to "DE"s. Frustrating.

I could just type in the actual R1C1 reference for my named ranges, but
then my formula bar lacks the range names and transparency I'm looking
for. I've tried doing that and doing Range-Name-Apply, but that
doesn't seem to work (plus, would that really require me to highlight
all 50 range names first?).

I think the Lotus transition options have something to do with it. In
my file, ONE CELL show this. There is a formula that shows reference
to cell K9, but when I check "Transition formula entry," it changes K9
to the range name. When I edit, it reads as "K9" and I can change it
to a mixed/absolute as I'd like. However, I can't recreate this in
another file. What's the magic bullet I'm missing here.

FYI: Excel 2002 in a Win2000 environment.


--
sonicblue
------------------------------------------------------------------------
sonicblue's Profile: http://www.excelforum.com/member.php...o&userid=28990
View this thread: http://www.excelforum.com/showthread...hreadid=487200