Forum Discussion
Using Indirect() function with a dynamically set named range
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.
- 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)
- I am looking for a native Excel solution to this.
- xlookup or vlookup does not work with the structure of my workbook
8 Replies
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.
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.
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 ChannelI 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.
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.
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.
- 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
- I have 10 input sheets called Store1, Store2, Store3, Store4.
- They all have the same local range names.
- 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
