Forum Discussion
XLOOKUP across many sheets
Hi All, I have an xlookup function as shown below. =XLOOKUP(C35, SydBankstown_AllYears!$F$2:SydBankstown_AllYears!$F$2000, SydBankstown_AllYears!$L$2:SydBankstown_AllYears!$L$2000) Each ...
Hello you can do the following to create your functions without INDIRECT().
Go to an auxiliary column (which you can delete later) and assemble the formula there as text.
In my example I have assumed that the sheet name is in column D.
=CONCAT("=XLOOKUP(C",ROW(),",",D35,"!$F$2:",D35,"!$F$2000,",D35,"!$L$2:",D35,"!$L$2000)")Then you have your formula as text in the auxiliary column. Of course, this does not work yet.
Now you can copy the cells with the formula texts and paste them into another column as text. Now you have the formula correctly in the cell - but still as text. To make the formula work, you only have to use the command "Text to columns" from the Data menu once without separators and voila, the text has become a formula that works.
Good luck