Non-Volatile Dynamic Named Ranges

[Mike Blackman]

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)

 

[Mike Blackman]

Hi Aladin,

Could you possibly explain how the evaluation proceedure works better with a sorted list? Unfortunately for this I can't use lists but does the same principal apply to data that is sorted alphabetically/Numerically?

The dependancy limit was also mentioned to me and I wondered if may have hit the limit using VLOOKUP but somehow not hit the limit using INDEX(MATCH)? And maybe that that was why the INDEX was out performing the VLOOKUP so dramatically?

If you were to run application.VLOOKUPs or .Formula in code instead of housing it within excel would the dependancy limit still exist?

Here's the formula I'm using, Please not that currently the Named Range is not dynamic and therefore is no longer volatile, I think .

=IF(ISNA(INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0)
,D$4+1)),0,INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0),D$4+1))

 

[Aladin Akyurek]

Hi Aladin,

Could you possibly explain how the evaluation proceedure works better with a sorted list? Unfortunately for this I can't use lists but does the same principal apply to data that is sorted alphabetically/Numerically?

The dependancy limit was also mentioned to me and I wondered if may have hit the limit using VLOOKUP but somehow not hit the limit using INDEX(MATCH)? And maybe that that was why the INDEX was out performing the VLOOKUP so dramatically?

If you were to run application.VLOOKUPs or .Formula in code instead of housing it within excel would the dependancy limit still exist?

Here's the formula I'm using, Please not that currently the Named Range is not dynamic and therefore is no longer volatile, I think .

=IF(ISNA(INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0)
,D$4+1)),0,INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0),D$4+1))

What is the current range Query_Data refers to?

 

[Mike Blackman]

Hi Aladin,

This is the range:

=Query!$G$14:$S$4853

 

[Aladin Akyurek]

Hi Aladin,

This is the range:

=Query!$G$14:$S$4853

Two questions, one proposal...

Questions:

How come does this formula you posted...

=IF(ISNA(INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0)
,D$4+1)),0,INDEX(Query_Data,MATCH($A22&$C22,Query!$A$14:$A$4853,0),D$4+1))

does not reference Query!$G$14:$G$4853 as the match-range instead of Query!$A$14:$A$4853?

What is in D$4?

Proposal:

Check whether the following formulas would yield a better score...

=LOOKUP(9.99999999999999E+307,
  CHOOSE({1,2}
  0,
  INDEX(Query!$G:$S,MATCH($A22&$C22,Query!$A:$A,0),D$4+1))))

If an #N/A result does not thwart the further processing adversely...

=INDEX(Query!$G:$S,MATCH($A22&$C22,Query!$A:$A,0),D$4+1)

 

[Mike Blackman]

Hi Aladin,

Let me try and make sense of whats going on with the spreadsheet with regards to the formula excluding the IF(ISNA......,0,(INDEX........) :

=(INDEX(Query_Data,MATCH($A6&$C6,Query!$A$14:$A$4853,0),D$4+1))

Query Data is accessing the Array that the result is taken from, results field if you like.

MATCH($A6&$C6,Query!$A$14:$A$4853,0) is concatenating A6 and C6 on the P&L sheet and Query!$A$14:$A$4853 is on the Query sheet and is a concatenation that my macro perfoms to give me a field that matches the concat to use for my VLOOKUP:

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

Where Query_Data used to begin at Column A and run all the way to Column S. This identifies my Column No from the Results Area (Query_Data).

D$4 is the Labels at the top of the Destination Sheet which are A Total field formatted as "YTD "0 abd the cell value is 0 & Periods 1 to 12 formatted as
"Period "0 so effectively D4:S4 are numbers from 0 to 12. This result gives me my row number.

The Intersection of Row No and Column No in my Results table gives me my result.

Hope this makes sense, I think I'm confusing myself now.

 

[Mike Blackman]

Hi All,

Just like to say a big thank you to Andrew, his alternative Dynamic Named Range has the desired result, I can now house a Dynamic Named Range without having to watch my model re-calculate every time I do something to any of the worksheets.

=Query!$G$14:INDEX(Query!$S:$S,Height)