Forum Discussion
I need help!?!
Good day all,
Layout -
- Workbook 1 will be the main workbook with a multitude of worksheets.
- Workbook 2-6 will have a multitude of worksheets with dates as sheet names.
The Challenge -
How do I get data to pull from workbooks 2-5 to the main workbook based on the sheet names if a date is typed into Workbook 1?
The goal is to have Workbook 1 reference Workbooks 2-6 instead of going back and forth. I need to figure out how to change a reference link so that the searched criteria changes the sheet name within a workbook.
IS THIS POSSIBLE.
Thanks in advance.
One way is to use INDIRECT, but for that all workbooks shall be opened.
Another way keep wokbooks 2-6 in the folder (or SharePoint folder), by Power Query get data from all of them, from each sheet for the workbook, transform keeping only necessary data, combine and load result into helper sheet of the workbook 1. Here XLOOKUP needed data from.
But that's all very depends on how exactly your data is structured.
Thank you for your response. I am trying to avoid keeping all workbooks open. The other workbooks are on a SharePoint folder. Would the other workbooks need to be formatted as tables to pull specific data or is there a way to do this without creating tables?
Thank you.
