Dynamic Named Ranges

[JackDanIce]

Hi,

I have a properties sheet in my workbook that contains information relating to the workbook (e.g. file location, save folders etc). Within this, I also have a list of the name of all my named ranges in the workbook, the range they refer to (sheet and cell area) and a description of their use.

I would like to now re-define my named ranges to the cell that contains the range they refer to so that other users can easily change this range as/when the area changes.

e.g. Named range "Print_Weekly" in the properties sheet shows it has a range of Weekly!$A$1:$M$12 and description that it is the print area of the weekly checklist.

Now, if the last row changes to 14, the range becomes Weekly!$A$1:$M$14; I'd like to just update this on my properties sheet and the named range will adjust accordingly. I've tried to use INDIRECT but this doesn't work and though I've searched and read about using OFFSET when defining named ranges to make them dynamic, this isn't how I want my spreadsheet set up, due to how other users will be interacting with it.

Can anyone suggest how to achieve defining the named range to a cell that contains the range area it should be referencing to?

Thanks,
Jack

 

[bertie]

Here is a good link for dealing with dynamic named ranges.
http://www.ozgrid.com/Excel/DynamicRanges.htm

I don't know if this will work for you, but it worked as I was playing around with your problem. Create two named ranges for the same cell.

Sheet1


<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 162px"><COL style="WIDTH: 99px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD style="FONT-WEIGHT: bold">Name</TD><TD style="FONT-WEIGHT: bold">Range</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Print_Weekly</TD><TD style="BACKGROUND-COLOR: #ffff99; FONT-FAMILY: Verdana">$A$1:$M$12</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Result =></TD><TD>$A$1:$M$12</TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B3</TD><TD>=Print_Weekly</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>



First I inserted a named range in B2 called pwRange
RefersTo= =Sheet1!$B$2
Then I inserted a named range in B2 called Print_Weekly
RefersTo=pwRange

I entered the indicated formula in cell B3 and tested by changing the range in B2.

Like I said, I have no idea if this will work in real life. Just something I was playing about with after reading your problem.

Bertie

 

[RoryA]

INDIRECT should work as you are using specific references. In what way does it fail?

 

[JackDanIce]

bertie - your answer makes sense but it would mean doubling the number of named ranges? I'll try it on some test code anyway...

rorya - so for example, sheet1 has an area A1:D4 which I've named "Print_Weekly". In my properties sheet I have in cell

A2: "Print_Weekly" 
B2: Sheet1!$A$1:$D$4
C2: Area of weekly sheet to print

Then, in my name defined formula bit, whilst ('properties' was the active sheet), I put:

=INDIRECT(properties!$B$2)

And then finally if in VBA I run:

Sub Test()
Range("Print_Weekly").select
End Sub

It doesn't seem to select, although possibly syntax is incorrect. Tried printing directly from the Weekly sheet but printers are down so can't confirm!

 

[RoryA]

Was Sheet1 active at the time?

 

[JackDanIce]

Just tried:

Sub Test()
Sheets("Weekly").select
Range("Print_Weekly").PrintOut Copies:=1
End Sub

But printer still down with an engineer on it so can't tell.. I can wait for now.

Thanks guys,
Jack

 

[RoryA]

Do you not have a PDF or XPS printer?

 

[JackDanIce]

I have an IT team who's default reply to anything is:
"Have you tried re-booting your machine?"

I swear if you want to become an IT person in any company, the first thing you're taught is if you're using Windows, then repeat 200 times "Have you tried re-booting your machine" I'm thinking of inventing a robot to save on wage bills. In this day and age of economic austerity.. yeah ok I'm on my high-horse too!

Cheers guys,
J

 

[RoryA]

I used to be an IT department. Have you tried rebooting?

 

[JackDanIce]

I didn't even think of trying that, rorya, you are GENIUS!! Is it time for pub yet now?