Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
sonicblue
 
Posts: n/a
Default 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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named ranges: don't want absolute references sonicblue Excel Discussion (Misc queries) 0 November 22nd 05 03:05 PM
replace absolute references bj Excel Worksheet Functions 0 May 20th 05 07:18 PM
Absolute cell references and subsequent problems. Pank Mehta Excel Discussion (Misc queries) 2 April 19th 05 11:38 AM
Like 123, allow named ranges, and print named ranges WP Excel Discussion (Misc queries) 1 April 8th 05 06:07 PM
Named dynamic ranges, copied worksheets and graph source data WP Charts and Charting in Excel 1 November 28th 04 06:19 PM


All times are GMT +1. The time now is 12:02 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"