S
sonicblue
Here's my dilemma, and it's a large one as my company utilizes a larg
model where this is an issue. When named ranges are references, the
are - by default - absolute references. This is a problem becaus
named ranges are very good identifiers to decode a formula (and ou
model uses very long, complicated formulas).
Say I have a table with 50 rows, one for each state. The other column
are RATES, MODS and SURCHG, with range names all around. Elsewhere i
the file, I have a cell with this formula (for CA, e.g, and I can typ
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,
can't copy down and get what I want. I have to manually go in an
change all the "CA"s to "DE"s. Frustrating.
I could just type in the actual R1C1 reference for my named ranges, bu
then my formula bar lacks the range names and transparency I'm lookin
for. I've tried doing that and doing Range-Name-Apply, but tha
doesn't seem to work (plus, would that really require me to highligh
all 50 range names first?).
I think the Lotus transition options have something to do with it. I
my file, ONE CELL show this. There is a formula that shows referenc
to cell K9, but when I check "Transition formula entry," it changes K
to the range name. When I edit, it reads as "K9" and I can change i
to a mixed/absolute as I'd like. However, I can't recreate this i
another file. What's the magic bullet I'm missing here.
FYI: Excel 2002 in a Win2000 environment
model where this is an issue. When named ranges are references, the
are - by default - absolute references. This is a problem becaus
named ranges are very good identifiers to decode a formula (and ou
model uses very long, complicated formulas).
Say I have a table with 50 rows, one for each state. The other column
are RATES, MODS and SURCHG, with range names all around. Elsewhere i
the file, I have a cell with this formula (for CA, e.g, and I can typ
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,
can't copy down and get what I want. I have to manually go in an
change all the "CA"s to "DE"s. Frustrating.
I could just type in the actual R1C1 reference for my named ranges, bu
then my formula bar lacks the range names and transparency I'm lookin
for. I've tried doing that and doing Range-Name-Apply, but tha
doesn't seem to work (plus, would that really require me to highligh
all 50 range names first?).
I think the Lotus transition options have something to do with it. I
my file, ONE CELL show this. There is a formula that shows referenc
to cell K9, but when I check "Transition formula entry," it changes K
to the range name. When I edit, it reads as "K9" and I can change i
to a mixed/absolute as I'd like. However, I can't recreate this i
another file. What's the magic bullet I'm missing here.
FYI: Excel 2002 in a Win2000 environment