Non-Volatile Dynamic Named Ranges

Mike Blackman

Well-known Member
Joined
Jun 8, 2007
Messages
2,494
Hi All,

I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.

Can anybody provide a non-volatile alternative to this?

=OFFSET(Query!$A$14,,,Height-13,19)

With Height being:

=MATCH(BigText,Query!$B:$B)
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
How can a range be dynamic and non-volatile? Dynamic means that you need it to adjust the range as data is entered? If it is not volatile then how would it adjust as data is entered? How would excel know to recalculate your non-volatile dynamic range?
 
Upvote 0
You can use INDEX instead of OFFSET, if it's OFFSET's volatility that is causing you problems:

=Query!$A$14:INDEX(Query!$S:$S,Height)
 
Upvote 0
Hi All,

I am looking for a non volatile alternative to using OFFSET in my named ranges as my models are generally quite bulky and it is beginning to make me quite angry having to build these models in calc manual.

Can anybody provide a non-volatile alternative to this?

=OFFSET(Query!$A$14,,,Height-13,19)

With Height being:

=MATCH(BigText,Query!$B:$B)

I don't think switching to the non-volatile INDEX will yield enough imorovement.

One possibilty is to use lists (instead of dynamic named ranges) if you are on Excel 2003 or 2007.

You could also scrutinize other formulas with volatile functions in order to see whether they can be replaced with faster working versions.
 
Upvote 0
Hi All,

Thank you for all your imput. Just a quick one as per schielrn's post and Andrews suggestion of the INDEX instead of OFFSET, would the Named Range truely not be dynamic due to the lack of a Volatile function or would it still be Dynamic?

Hi Aladin,

I think the lists thoery is a good one, but unfortunately for this I only have one Named range which is a data field maybe 10000 rows by 20 columns and several hundred Vlookups running which references the Named Range, there are no other volatile functions in the model apart from the OFFSET used to define the range in question. I was only looking to prevent the Re-calc everytime I even considered touching my key-board, or so it feels anyhow!

Sorry, this posts turned into a Novel.

Thank you to all.
 
Upvote 0
Hi All,

Thank you for all your imput. Just a quick one as per schielrn's post and Andrews suggestion of the INDEX instead of OFFSET, would the Named Range truely not be dynamic due to the lack of a Volatile function or would it still be Dynamic?

Yes, the range will be dynamic.

Hi Aladin,

I think the lists thoery is a good one, but unfortunately for this I only have one Named range which is a data field maybe 10000 rows by 20 columns and several hundred Vlookups running which references the Named Range, there are no other volatile functions in the model apart from the OFFSET used to define the range in question. I was only looking to prevent the Re-calc everytime I even considered touching my key-board, or so it feels anyhow!

Sorry, this posts turned into a Novel.

Thank you to all.

Care to post the VLOOKUP formula you are invoking?
 
Upvote 0
Hi Aladin,

The formula is:

Code:
=VLOOKUP($A6&$C6,Query_Data,D$4+7,0)

Just so you know D$4:O$4 are Period numbers formatted as such:

"Period "0

Thanks again, If you need any more information please let me know.
 
Upvote 0
Hi Aladin,

The formula is:

Code:
=VLOOKUP($A6&$C6,Query_Data,D$4+7,0)

Just so you know D$4:O$4 are Period numbers formatted as such:

"Period "0

Thanks again, If you need any more information please let me know.

Is Query_Data sorted in ascending order on its first column? If not, try to do so for such would allow you to invoke a faster look up:

Code:
=IF(VLOOKUP($A6&$C6,Query_Data,1,1)=$A6&$C6,
    VLOOKUP($A6&$C6,Query_Data,D$4+7,1),
    "")
 
Upvote 0
Hi Aladin,

Unfortunately not. I have changed the formula now to a MATCH embeded in an INDEX to find the results and this is working a lot quicker; Don't have the formula handy but happy to post it tomorrow.

I still have the issue with the dynamic named range, although I am yet to test the Andrew's INDEX method in the dynamic named range. I am not necessarily looking for greater speed in the calculation times (although all improvements are well received), I was just looking to cut down on the need to re-calculate every time a do anything with the model. If this cannot be acheived then I will define the named range from within my code using lRow.

Thanks again Aladin.
 
Upvote 0
Hi Aladin,

Unfortunately not. I have changed the formula now to a MATCH embeded in an INDEX to find the results and this is working a lot quicker; Don't have the formula handy but happy to post it tomorrow.

I still have the issue with the dynamic named range, although I am yet to test the Andrew's INDEX method in the dynamic named range. I am not necessarily looking for greater speed in the calculation times (although all improvements are well received), I was just looking to cut down on the need to re-calculate every time a do anything with the model. If this cannot be acheived then I will define the named range from within my code using lRow.

Thanks again Aladin.

Recalculation and speed/performance are correlated.

I'm very much interested in a code snippet which would instruct the list with "Sort Ascending" (already constructed with Data|List|Create List).

Btw, INDEX(...,MATCH(...,0)) is a bet faster than VLOOKUP(...,0), but looking up in a sorted list/table is way faster (re-calc times will go down).
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,163
Members
448,554
Latest member
Gleisner2

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