Forum Discussion
Using Indirect() function with a dynamically set named range
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 Channel
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.
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!
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
