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.