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

 

[RoryA]

It is, as they say, 5.00 somewhere. (God bless Jimmy Buffett)

 

[JackDanIce]

Could a truer word ever have been spoken? (within the correct context of course!)

 

[RoryA]

Well, there's also Joe Jackson's "What's the use of getting sober, when you're gonna get drunk again?"

 

[JackDanIce]

Or that American guy (name escape's me) who said:
"Unlike sober people, when I wake up hungover, at least I know for sure my day can only get better!"

 

[RoryA]

Frank Sinatra. ("I feel sorry for people who don't drink. When they wake up in the morning, that's as good as they're going to feel all day")

 

[JackDanIce]

I think I'd be showing my age if I said, yes that's who I meant! Erm, he had a good cameo role in the Cannonball films.. about best I remember from my youth?!