Dynamic Named Range not listed as Named Range

ljrezn

New Member
Joined
Jun 27, 2011
Messages
15
It is my understanding that dynamic named ranges using the OFFSET() Function are not listed in the named range drop-down list on the toolbar. Is there a way to get around this?

I need to be able to select the dyanmic named range in order to clear the range and paste an updated list in its place without leaving leftovers at the bottom.

I also have Equations to the right of the list so using the shortcut End+Shift+Home does not work.

If anyone can help solve this problem that would be great!

Thanks!
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Welcome to the board...

Nope, dynamic named ranges will not appear in the named range list..

However, you can still type the name in the "Name" box to the left of formula bar and press enter.
 
Upvote 0
Jonmo,

What about using a change event to re-define static ranges, do you think that would be viable or resource hungry compared to the offset method?
 
Upvote 0
I would think re-defining the ranges whenever data changes would be too much overhead.

Too much trouble for what is essentially a minor nuisance.
 
Upvote 0
Thanks Jon, I had a feeling that might be the case, thought it easier to get a professional opinion than to create a test workbook.
 
Upvote 0

Forum statistics

Threads
1,224,222
Messages
6,177,224
Members
452,765
Latest member
Erka Gizli

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top