Using Indirect() function with a dynamically set named range

[PapaAustin]

Hello,

[Edit Apr 27] - Example attached.

Let's say that in Sheet1 I have a local named range is that defined as 

• Name: Dynamic_Array
• Refers to:  =$A$1:INDEX($A$1:$E$1,3)

Range A1:E1 has number 1,2,3,4,5

In Sheet2, I have

• Cell A1 = Sheet1!
• Cell B1 = Dynamic_Arry
• Cell C1 = indirect(A1&B1)

the Indirect formula in C1 results in #ref error.  I understand as to why this is happening (Indirect formula cannot work with a named range that is defined with formulas because it requires a text as an argument)

Is there a workaround with this? 

I know I could create a UDF that returns the address of A1&B1 and do something like  = indirect ( UDF (A1&B1)) and make it work. But I am looking for a native solution to what UDF would do. 

1. I can't change the structure of the workbook (I cannot create a helper look-up table per se. I have thousands of these named ranges to reference)
2. I am looking for a native Excel solution to this. 
3. xlookup or vlookup does not work with the structure of my workbook 

[#2]

@PapaAustin 

You could try this:

Select C1 on Sheet2 (this is important).

Create a new defined name named Eval that refers to =Evaluate(Sheet2!A1&Sheet2!B1)

Enter the formula =Eval in C1 (or in C1:E1 as in the screenshot below)

This can be filled down if you have other sheet names and range names.

 

[#3]

@PapaAustin 

It appears that your defined name 'Dynamic_Array' returns an array rather than a range reference so is ineligible as an argument of INDIRECT.  It can be calculation using the old XLM4 macro command EVALUATE.  This only works using Name Manager rather than grid calculation.

That is 

= EVAL

works, where the Name 'EVAL' refers to

=EVALUATE(Sheet2!$A1&Sheet2!$B1)

Rather better, if you can switch your Excel 365 to the Insider's Beta Channel, is to use a Lambda function 'EVALλ' which refers to 

=LAMBDA(n,EVALUATE(n))

This allows you to use

= EVALλ(A1&B1)

on the worksheet.

Note: Using λ from the Greek alphabet, is just a convention I have chosen to distinguish Lambda functions from other defined names.  It has no special significance to Excel but works fine.

[#4]

@Hans Vogelaar 

This is really interesting. I haven't come across using =evaluate( ) function in the named range.

However, unfortunately, this can't be a solution for my particular application due to the workbook structure.

I could have many [input] sheets with 'dozens' of local range names.

1. Let's say I have local named ranges called Price1, Price2, ... PriceN

• This is a time series array. Right now they are statically defined say A(row) : CV(row) - 100 values

1. I have 10 input sheets called Store1, Store2, Store3, Store4.
   • They all have the same local range names. 
2. I have a sheet called [Calculations] 
   • in here, I use Sheet names and named ranges using = Indirect ( Sheet!Local_Named_Range)
   • This works flawlessly and does what I need them to do. Very easy to work with

But this forces me to work with range names that are defined using the longest possible length all the time. I can reduce my workbook's calculations dramatically if I can make the range named dynamically defined. There is no practical way for me to use your recommendation without fundamentally re-working my workbook (which I am not allowed to do). I was hoping there is a way for me to change the formula to make this work

[#5]

@Peter Bartholomew 

Thank you, Peter.
Yes, I understand that I am using an invalid argument of Indirect.
I tried to elaborate my setup in the response below (to Hans' reply) and why it would be impractical for me to create a named range just to get around this issue (I have 100+ named ranges to reference this way. I am definitely learning from you and Hans. So, please share your thoughts.

As to Lambda, I do have Office 365 but I don't have Lambda! (incredibly frustrating)

I'm using an Enterprise license (version information below). Do I have a hope of getting Lambda in my version soon-ish? 

Office 365
Version 2002 (Build 12527.21686 click-to Run)
Semi-annual Enterprise Channel

[#6]

@PapaAustin 

An uninformed guess,  but I would expect it to be a long time (over a year?) before Lambda functions reach a semi-annual license.  The changes made possible by the LET function and LAMBDA can be seismic but it needs to be right.  There is very little that is recognisable from traditional spreadsheet solutions visible in my workbooks.

To the best of my knowledge the semi-annual update is a choice made by your IT Admin, not a license restriction.  I believe it is possible for the Admin to select a group of users to have Insider Beta access for purposes such as guiding future policy.

As for your present problem, could you mock up a couple of detail sheets and a summary sheet with a few lines of data and sample formula?  For me, this type of development is largely a matter of trial and error rather than firm prior knowledge.  Example questions that might become clear are

1. Are the dynamic changes to the data linked to the time line with every sheet synchronised?

2. Is the content of the detail sheets raw data or dynamic arrays (the use of Tables may aid the first and dynamic range references the second)?

Comments: CHOOSE offers an alternative approach to picking up values from a list of sheets (hard-wired as parameters into the function.  Another approach to the dynamic range would be to access the full range, as you do now, but then to apply the OFFSET function to create the reduced size array.

For the future, besides allowing the effective use of EVALUATE on the worksheet, LAMBDA functions enable array lookups to be presented in a more developer-friendly manner such as

= ARRAY(sheetIndex, blockIndex, rowIndex, columnIndex)

with all the 'plumbing' discretely hidden.  Recursion can be used to allow arrays of arrays to be aggregated but, as things stand, that is fiercely difficult to implement.  If MAP and REDUCE functions are added to manage the recursion stack, that might become usable without divine inspiration!

[#7]

@PapaAustin 

I think no chances to have LABMDA on this channel soon. Semi-annual is updated by new functionality twice per year, lambda is still only for beta insiders and I don't think it'll go into production in nearest future. I'd expect on semi-annual next (2022) summer or so.

[#8]

@Peter Bartholomew 

Hello Peter,
I uploaded my example in the original post.
It mimics pretty closely what I am trying to do. Except my actual workbook has more data in them.

Please see [Consolidation] sheet where the #ref error is.

The current version of my workbook works with static named ranges (vs. dynamic named ranges)

i.prod.Gadget1 <- would just have a fixed array of C9 to AZ9 (always 50 months even if I am only modelling the next 20 months)

I have over 2000+ such named ranges. So the static ranges cause a noticeable delay in my calcuations

[#9]

@PapaAustin 

Sorry that I was so careless as to overlook the workbook, and then request it!

I cannot find any satisfactory way of returning dynamic arrays using INDIRECT.  I can use CHOOSE to select a specific sheet

= CHOOSE(XMATCH(factory,factory.list),
        Factory1!product.data,
        Factory2!product.data,
        Factory3!product.data)

but its use made little sense where you wanted to sum over each factory.

Maybe, some time in the future, I will be able to demonstrate a Lambda function to return production volumes etc.

= VOLUMEλ(factory, product, month)

and maybe, even to demonstrate new functionality to sum production over a list of factories.