Forum Discussion
Help to find relevant data across multiple tabs of data. I think it is a index, match 3D formula
Hi Everyone,
I have a spreadsheet that has large number of tabs which are set up identically, there is just one tab for each product. Then lastly there is a summary tab. I want help with a formula to find and return the relevant data from each tab. I believe it is a combination of Index and match across the tabs.
Attached is an example.
Appreciate any help.
Kind regards,
5 Replies
- Which version of Excel are you using precisely? This is something you could do using PowerQuery (Get & Transform, New Query, From File, From Workbook)
- Hi Jan,
Thanks for the message.
I use Microsoft 2016 I believe. I have not used power query before however, but keen to learn.
Thankscalof1 Here are the steps:
- Save the file
- Click Data, Get Data (your button might be named "New QUery), From File, From Workbook:
- Click on the file name and then click Transform data:
- I assumed the tabs we wantto include all have three-letter names. Click the Add column tab, Custom Culomn button:
- Use this formula to calculate the # of characters in the tab name:
- Click the filter button on the new column and select "Number filters", "Equals...":
- Set it to 3:
- Select the first two columns by control-clicking their headers and right-click. Select "Remove other columns":
- Click the expander icon next to Data:
- Keep all columns selected and OK the dialog.
- Delete all columns you do not need
- Remove the first 15 rows:
- Click "Use first row as headers":
- Rename first column back to "Name":
- Click "Close and Load", Close and load to...:
- Choose these settings:
- Now you're ready to create a pivot table which looks like your current report:
