Apr 26 2021 02:08 PM - edited Apr 27 2021 08:05 AM
Apr 26 2021 02:08 PM - edited Apr 27 2021 08:05 AM
[Edit Apr 27] - Example attached.
Let's say that in Sheet1 I have a local named range is that defined as
Range A1:E1 has number 1,2,3,4,5
In Sheet2, I have
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.
Apr 26 2021 02:39 PM
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.
Apr 26 2021 03:10 PM
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.
works, where the Name 'EVAL' refers to
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
This allows you to use
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.
Apr 26 2021 03:12 PM
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.
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
Apr 26 2021 03:31 PM
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?
Version 2002 (Build 12527.21686 click-to Run)
Semi-annual Enterprise Channel
Apr 27 2021 02:53 AM
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!
Apr 27 2021 04:00 AM
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.
Apr 27 2021 08:16 AM
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
Apr 27 2021 03:02 PM
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.