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

Question

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.

What is your end goal then? You just want to return the value of single cell? — Harun24hr
– Harun24hr, Commented Nov 25, 2022 at 2:34
What about this one =BYROW({"221122"}, LAMBDA(r,INDEX(INDIRECT("'" & r & "'!BB38"),1,1)))? — Harun24hr
– Harun24hr, Commented Nov 25, 2022 at 2:35
Using INDEX like that works, yes. But see my last example - what I really want to do is return a range of three adjacent cells, for example, 221122!BB3:BD38. Is there a way to do that? — nickpharris
– nickpharris, Commented Nov 25, 2022 at 2:53
Lets try =REDUCE(0,{"221122"}, LAMBDA(a,b,INDIRECT("'" & b & "'!BB38:BD38"))). If it works then let me know. I will post this as answer. — Harun24hr
– Harun24hr, Commented Nov 25, 2022 at 2:59

P.b · Accepted Answer · 2022-11-25 18:23:09Z

1

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

edited Nov 25, 2022 at 18:23

answered Nov 25, 2022 at 11:59

P.b

12k2 gold badges15 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

nickpharris Over a year ago

Wow - it works! Thanks. Amazing that REDUCE can actually return a dynamic array (I understood from the documentation it was just a single value). And credit to @Harun24hr for calling out the REDUCE / VSTACK idea in the comments.

nickpharris Over a year ago

Hang on - you can do ranges of sheets?!!! Two amazing answers. Tx.

P.b Over a year ago

It can be used to do BYROW spill formula calculations. Just keep in mind REDUCE always starts at it's initial value (0 in this case) and VSTACK or HSTACK can be used to SPILL the values. DROP can be used to remove the initial value where the recalculation of it's lambda value hasn't started yet.

P.b Over a year ago

I thought the second option might come handy as well

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related