excelexcel-formuladynamic

Why am I encountering this "#VALUE error" when using BYROW, LAMBDA and INDIRECT function together?

Issue I am using BYROW, LAMBDA and INDIRECT functions together in order to create a dynamic table of content of the entire workbook listing in the first column the sheet name and in the second column the value in specific cell of each workbook (same cell for each as workbook intended to use a defined structure template)

E.g.

Tab Description
ABC Limited Nature of operations are...
DEF Co Nature of operations are...

The first column I am aware how to achieve using name manager and =(GET.WORKBOOK(1))&T(NOW()) etc. This will dynamically update in array format the list of sheets in the workbook.

For the second column I am trying to use a reference to the first column and the indirect formula to get a specific cell from each workbook e.g. A1 from each workbook. I'm using BYROW and and LAMBDA along with indirect since I'm aware that INDIRECT formula does not cater for arrays.

Simplified Formula Demo I'm including a simplified version of the formula which is just picking up data from the same sheet as I managed to narrow down the issue. It seems that :

This is the formula which doesn't work returning a #VALUE error

=BYROW(TRANSPOSE({"A1","A2"}),LAMBDA(X,INDIRECT(X)))

This is a workaround I found which is a bit messy and I'd like to avoid

=BYROW(C4#,LAMBDA(X,INDIRECT(X)))

Note C4# contains array: {"A1","A2"}

Issue encountered As you can see the main difference is that the second one which works I am referring to C4# an external array, whereas in the first formula I am trying to contain the array inside the BYROW formula, to make the formula more concise and neat. My goal is to find a way to make the first formula work without having to add the array (C4#) external to the formula.

Solution

  • The note

    Despite to INDIRECT function, INDIRECT doesn't work with neither more a single cell range nor a dynamic array argument (even if the dynamic array consists of a single value only).

    The answer

    =BYROW(TRANSPOSE({"A1","A2"}),LAMBDA(X,INDIRECT(X)))

    doesn't work and returns the '#CALC!' error due to limitation of INDIRECT described above (X gets the row {"A1","A2"} which is not accepted by INDIRECT).

    OP writes that this formula returns '#VALUE!', but my guess it's OP's mistyping due to numerous tries to solve the issue. This formula without TRANSPOSE indeed returns '#VALUE!' and its argument is the same vertical vector of two elements like 'C4#' in the formula which works.

    Thus, I will research the next formula:

    =BYROW({"A1","A2"},LAMBDA(X,INDIRECT(X)))

    It doesn't work instead of =BYROW(C4:C5,LAMBDA(X,INDIRECT(X))) due to the feature of the BYROW/BYCOL function to process the range or the dynamic array argument differently.

    The solution provided by @ScottCraner is

    =BYROW({"A1","A2"},LAMBDA(X,INDIRECT(@X)))

    enter image description here

    Or another solution

    =MAP({"A1","A2"},LAMBDA(X,INDIRECT(X)))

    enter image description here

    Explanation of BYROW/BYCOL behavior

    BYROW and BYCOL functions have a feature in different processing of the range or the dynamic array argument.

    enter image description here

    As we can see, BYROW gets an array from each element of the vertical vector dynamic array while it gets a single value per row from the single-column range.

    Summary

    I can state what behavior of the BYROW (BYCOL) function with a range argument contradicts its description (BYCOL description).