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?
