MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Dynamic Range Names

Posted by Herb B. on November 03, 2001 11:45 AM

I have a spreadsheet with many schedules and each schedule occupies a range of cells that will not change. I want to create Range Names for each schedule that I can use for navigating the spreadsheet and printing. The trick is that I want each Range to be named based on text in a cell located in each schedule. I want a macro to do this for me and whenever I evoke the macro the Range will be renamed based on the entry in the cell.

I have seen this done in much earlier versions of Excel or Lotus but can quite figure it out. Thanks.

Posted by bob umlas on November 03, 2001 4:49 PM

Posted by Herb B. on November 04, 2001 5:57 AM

Posted by Herb B. on November 05, 2001 2:46 AM

Is this too hard or too easy?

I am a novice with VBA. Can someone take a crack at this or tell me if it seems possible. I have looked throughout the Message Board and can't find anything that helps.

Posted by Mark W. on November 05, 2001 6:26 AM

I'm puzzled by your request...

...are you suggesting.... by your requirement that
the range name should be based on the contents of
a cell... that the defined name of the range
shouldn't be static?

Posted by Herb B. on November 05, 2001 11:53 AM

You are correct Mark W. Any suggestions?

Posted by Mark W. on November 06, 2001 5:55 AM

I'm puzzled because...

Ordinarily, once the design of a worksheet is
finalized defined names for cell ranges
would not be changed. While references to
a changed defined name would be updated for
all open workbooks, references in closed
workbooks would be obseleted and return an
#REF! error when recalculated. The whole
idea behind defined names is to preserve
referential integrity while permitting a
degree of data independence -- allowing
data to be accessed without specification
of a physical cell address.

Why is that you'd like to redefine your
range names?