Forum Discussion
Look Up for multiple sheets?
I wonder if someone would be so kind as to help me with a little problem...
I have 12 different workbooks, one for each store, for labour and sales forecasting. On each workbook there are a few different sheets - one for each period and then a final sheet which shows the total for each week and period, giving a quarterly summary.
The only problem with this is when I want to review these I have to open individually 12 different workbooks.
Is there a way that I can create a new workbook, which shows the 'Quarterly Summary' page for each store. I initially thought a look up formula, but I think this only applies to particular cells and not an entire sheets?
Thanks in advance!
5 Replies
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))
