Forum Discussion
Look Up for multiple sheets?
Open one of the other workbooks.
In the 'target' sheet in your summary workbook, create a formula such as
=LET(range, '[OtherWorkbook.xlsx]SheetName'!A1:H20, IF(range="", "", range))
where A1:H20 is the range you want to display.
When you close the other workbook, Excel will add its path to the formula. You can then save the summary workbook.
Repeat for each of the other workbooks.
Thank you so much for your reply, your help is so gratefully received! I fear I may be making a mistake... probably an elementary one.
Whereby the other workbook is called 'Purley', the sheet in question is called 'Quarterly Summary' the range is 'A1:Q31'. When I enter the below formula in my summary workbook it just produces a 0 in A1
=LET(range,'[Purley.xlsx]Quarterly Summary'!A1:Q31,IF(range=A1, Q31, range))
Try
=LET(range,'[Purley.xlsx]Quarterly Summary'!A1:Q31,IF(range="", "", range))
You are nothing short of a genius! Thank you so much!
One final question - I don't suppose there's any way to bring across the formatting too?Formulas only return values, they don't "return" formatting. That would require either conditional formatting or VBA code.