As a toy example, I have a workbook with multiple sheets: Calculations, Data1, Data2, Data3
I want to define a lambda function in Name Manager so that I can call it from Calculations and pass it the name of one of the data sheets.
I tried defining the following lambda function:
calcB = LAMBDA(dataSheet, SUM(dataSheet!B1, dataSheet!B10)
When I save the lambda function, a Finder window pops up with the message ‘Cannot find ‘dataSheet’. Copy from…
To see what would happen, I tried calling
=calcB(Data1)
from the Calculations sheet and the same Finder window pops up with the same message ‘Cannot find ‘dataSheet’. Copy from…
I’ve tried different variations of where to put quotes or the “!” symbol, but I haven’t had success.
What’s the correct way to pass the name of a sheet to a lambda function?
3
While some hints are provided in comments, I would like to provide the explanation.
First of all, the answer for the question is calcB("Data1") (@ScottCraner). The only way to provide pure sheet’s name is to express it as a text (in double quotes). Another way is the range reference, where sheet’s name can be extracted from.
Second, Excel doesn’t accept calcB = LAMBDA(dataSheet, SUM(dataSheet!B1, dataSheet!B10) because this syntax ‘dataSheet!B1’ means the dataSheet is sheet’s name not a variable. To resolve this demand the INDIRECT function should by applied. This function converts the range reference expressed by a text to the range reference itself which allows to use either variables or constants to compose the reference text (@VBasic2008):
INDIRECT("'"&dataSheet&"'!B1")