Named Range- 'refers to' defined in a cell instead of in the Name Manager

Newbie first post.

I have a spreadheet with 20+ named ranges. I update this spreadheet monthly so going in and changing every refers to' value in the Name Manager is a pain. I am wondering if it is it possible to define the 'refers to' value for the named range in a cell and then reference that cell instead of editing them each individually in the Name Manager. That way I could just adjust the contents of the cell every month and the Named Range would update based on that value.

I have attached a simplified excel of what I mean. What I would like is to be able to update cell M24 highlighted in yellow and the named range period3_revenue is shown in the table to the side.

Help would be appreciated.

Thanks

You should put this in cell M24:

Sheet1!$C$40:$C$43

i.e. remove the apostrophes and equals sign. Then in Name Manager you can define the range period3_revenue using this formula:

=INDIRECT(Sheet1!$M$24)

You can test it out by putting this formula in, say, cell P1:

=INDEX(period3_revenue,ROW())

and copying this down a few rows - you will get #REF! beyond the first 4 rows, but then if you change M24 to this:

Sheet1!$C$40:$C$47

you will see a few more values appear instead of the errors.

Hope this helps.

Pete

Hi,

Yes this is possible. The process involves creating Dynamic Named Ranges (DNR). The formula consists of OFFSET and COUNT(a,if,ifs).

The COUNT function uses criteria to identify the range. In your file you refer to "period revenue". What is the criteria you are using to identify these ranges?
That criteria can be used to develop DNR's.

Hope that makes sense.

Cheers

Thank you so much for your replies.

@pete_UK your solution worked perfectly !

The other solutions also sound good but a little more complicated for a newbie!

Thank you again

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete