#VALUE! Error Using Lambda And ByRow Helper Function in Excel (365)-CodePudding

I am creating a dynamic report summarizing data on multiple XLS sheets all within a single workbook. The sheets have names that tie to a specific date.

This is simplified example of what I am doing, which works fine - it gives the correct answer which is the value of the cell at reference BB38 on the sheet called "221122":

=LAMBDA(r,INDIRECT("'" & r & "'!BB38"))("221122")

Problem comes when I want to iterate this over an array of sheets using BYROW instead of just passing the sheet name to the lambda. Simple example to replicate the problem:

=BYROW({"221122"}, LAMBDA(r,INDIRECT("'" & r & "'!BB38")))

This gives a #VALUE! error, instead of the correct answer which is the reference to that same cell (as part of a one cell dynamic array result). The only way I can solve it is by adding a SUM around the INDIRECT:

=BYROW({"221122"}, LAMBDA(r,SUM(INDIRECT("'"&r&"'!BB38"))))

Apart from being ugly, what I REALLY want to do is get a group of cells (spilled) back, like this, but then I can't use the SUM trick:

=BYROW({"221120","221121","221122"}, LAMBDA(r,INDIRECT("'"&r&"'!BB38:BD38")))

So that I am aiming towards is a spilled range like this:

Column A	Column B	Column C
221120!BB38	221120!BC38	221120!BD38
221121!BB38	221121!BC38	221121!BD38
221122!BB38	221122!BC38	221122!BD38

I know that you can't pass a dynamic function to INDIRECT but that's not what I am doing here - I am passing a single row of the dynamic array, represented by r.

In comment, Harun24hr points out correctly that BYROW can't return a dynamic array - that's why SUM worked. My own 'hack' way around this was to get the individual 1xN ranges of cells representing BB38, BC38 and BD38 and then HSTACK them together, e.g.:

    a, BYROW(sheets, LAMBDA(r, SUM(INDIRECT("'" & r & "'!AY38")))),
    b, BYROW(sheets, LAMBDA(r, SUM(INDIRECT("'" & r & "'!AZ38")))),
    c, BYROW(sheets, LAMBDA(r, SUM(INDIRECT("'" & r & "'!BA38")))),
    d, BYROW(sheets, LAMBDA(r, SUM(INDIRECT("'" & r & "'!BB38")))),
    HSTACK(a,b,c,d)

Real question is: is there a more elegant / scalable way than HSTACK 1xN columns together?

Any ideas please? Thank you.

CodePudding user response：

How about this approach:

=LET(start,221120,
     end,221122,
DROP(REDUCE(0,SEQUENCE(1 end-start,,start),LAMBDA(s,e,VSTACK(s,INDIRECT("'"&e&"'!BB38:BD38)))),1))

Or simple =VSTACK('221120:221122'!BB38:BD38) based on this answer by JvdV: https://stackoverflow.com/a/74077560/12634230