Home

Help to find relevant data across multiple tabs of data. I think it is a index, match 3D formula

%3CLINGO-SUB%20id%3D%22lingo-sub-733585%22%20slang%3D%22en-US%22%3EHelp%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733585%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20that%20has%20large%20number%20of%20tabs%20which%20are%20set%20up%20identically%2C%20there%20is%20just%20one%20tab%20for%20each%20product.%20Then%20lastly%20there%20is%20a%20summary%20tab.%20I%20want%20help%20with%20a%20formula%20to%20find%20and%20return%20the%20relevant%20data%20from%20each%20tab.%20I%20believe%20it%20is%20a%20combination%20of%20Index%20and%20match%20across%20the%20tabs.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAttached%20is%20an%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20any%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-733585%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733624%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733624%22%20slang%3D%22en-US%22%3EWhich%20version%20of%20Excel%20are%20you%20using%20precisely%3F%20This%20is%20something%20you%20could%20do%20using%20PowerQuery%20(Get%20%26amp%3B%20Transform%2C%20New%20Query%2C%20From%20File%2C%20From%20Workbook)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733637%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733637%22%20slang%3D%22en-US%22%3EHi%20Jan%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20for%20the%20message.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20use%20Microsoft%202016%20I%20believe.%20I%20have%20not%20used%20power%20query%20before%20however%2C%20but%20keen%20to%20learn.%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-733732%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-733732%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F364226%22%20target%3D%22_blank%22%3E%40calof1%3C%2FA%3E%20Here%20are%20the%20steps%3A%3C%2FP%3E%0A%3COL%3E%0A%3CLI%3ESave%20the%20file%3C%2FLI%3E%0A%3CLI%3EClick%20Data%2C%20Get%20Data%20(your%20button%20might%20be%20named%20%22New%20QUery)%2C%20From%20File%2C%20From%20Workbook%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20502px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121868iA153AB28DA682175%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-38-18.jpg%22%20title%3D%222019-07-02_12-38-18.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20on%20the%20file%20name%20and%20then%20click%20Transform%20data%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20878px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121869iAFD51D965A905E49%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-41-26.jpg%22%20title%3D%222019-07-02_12-41-26.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EI%20assumed%20the%20tabs%20we%20wantto%20include%20all%20have%20three-letter%20names.%20Click%20the%20Add%20column%20tab%2C%20Custom%20Culomn%20button%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20405px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121875i06130418904C7E07%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-43-44.jpg%22%20title%3D%222019-07-02_12-43-44.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EUse%20this%20formula%20to%20calculate%20the%20%23%20of%20characters%20in%20the%20tab%20name%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121876iD75F595F4D85C2B7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-45-35.jpg%22%20title%3D%222019-07-02_12-45-35.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20the%20filter%20button%20on%20the%20new%20column%20and%20select%20%22Number%20filters%22%2C%20%22Equals...%22%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20550px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121877i35114CE4E4A01997%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-46-40.jpg%22%20title%3D%222019-07-02_12-46-40.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3ESet%20it%20to%203%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121878i57409ACE58801434%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-47-12.jpg%22%20title%3D%222019-07-02_12-47-12.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3ESelect%20the%20first%20two%20columns%20by%20control-clicking%20their%20headers%20and%20right-click.%20Select%20%22Remove%20other%20columns%22%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20418px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121879iA4EB7BC038FB3D34%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-49-37.jpg%22%20title%3D%222019-07-02_12-49-37.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20the%20expander%20icon%20next%20to%20Data%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20225px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121880iC11BDC92AC761535%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-51-18.jpg%22%20title%3D%222019-07-02_12-51-18.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EKeep%20all%20columns%20selected%20and%20OK%20the%20dialog.%3C%2FLI%3E%0A%3CLI%3EDelete%20all%20columns%20you%20do%20not%20need%3C%2FLI%3E%0A%3CLI%3ERemove%20the%20first%2015%20rows%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20589px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121881i7E6285250F32872D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-54-14.jpg%22%20title%3D%222019-07-02_12-54-14.jpg%22%20%2F%3E%3C%2FSPAN%3E%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20698px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121882i2A864DC0D2ECB60C%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-55-08.jpg%22%20title%3D%222019-07-02_12-55-08.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20%22Use%20first%20row%20as%20headers%22%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20750px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121884i8255B00A7C3E6D53%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-56-16.jpg%22%20title%3D%222019-07-02_12-56-16.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3ERename%20first%20column%20back%20to%20%22Name%22%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20296px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121886i970420D1D8266C54%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-57-35.jpg%22%20title%3D%222019-07-02_12-57-35.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EClick%20%22Close%20and%20Load%22%2C%20Close%20and%20load%20to...%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20358px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121887i114CC66F68CFD60F%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_12-58-40.jpg%22%20title%3D%222019-07-02_12-58-40.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3EChoose%20these%20settings%3A%3CBR%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20307px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121889i12D5DA1DB39B34AB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_13-00-21.jpg%22%20title%3D%222019-07-02_13-00-21.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%3ENow%20you're%20ready%20to%20create%20a%20pivot%20table%20which%20looks%20like%20your%20current%20report%3A%3C%2FLI%3E%0A%3CLI%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20958px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121890iCB382C050ADF1EAB%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%222019-07-02_13-03-35.jpg%22%20title%3D%222019-07-02_13-03-35.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3C%2FOL%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735435%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735435%22%20slang%3D%22en-US%22%3E%3CP%3EHi%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F22322%22%20target%3D%22_blank%22%3E%40Jan%20Karel%20Pieterse%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20the%20step%20by%20step%20instructions.%20I%20have%20been%20able%20to%20create%20a%20pivot%20table%20however%20it%20is%20slightly%20different%20to%20yours.%20For%20the%20%22Statistics%20based%20on%20current%20Year%22%20is%20this%20a%20named%20range%20created%20in%20the%20query%3F%20My%20attempt%20thus%20far%20is%20showing%20data%20from%20my%20first%20tabs%20of%20data%2C%20which%20i%20do%20not%20need%20to%20include.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20attached%20what%20i%20am%20working%20on%2C%20the%20pivot%20table%20is%20located%20on%20row%20125%20down%20of%20the%20%22Summary%20of%20Stocks%22%20tab.%20Can%20you%20please%20see%20if%20you%20can%20tell%20what%20i%20need%20to%20amend%20in%20the%20query%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20again%20for%20your%20support%2C%20much%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735925%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20to%20find%20relevant%20data%20across%20multiple%20tabs%20of%20data.%20I%20think%20it%20is%20a%20index%2C%20match%203D%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735925%22%20slang%3D%22en-US%22%3ELooks%20like%20you%20at%20least%20did%20not%20filter%20the%20calculated%20column%20containing%20the%20length%20of%20the%20worksheet%20names%20to%203%20characters.%20Also%20you%20didn't%20remove%20the%20unneeded%20columns.%20The%20steps%20are%20there%20in%20my%20instructions%20%3A-)%3C%2FLINGO-BODY%3E
calof1
Contributor

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.

Thanks

@calof1 Here are the steps:

  1. Save the file
  2. Click Data, Get Data (your button might be named "New QUery), From File, From Workbook:2019-07-02_12-38-18.jpg
  3. Click on the file name and then click Transform data:
    2019-07-02_12-41-26.jpg
  4. I assumed the tabs we wantto include all have three-letter names. Click the Add column tab, Custom Culomn button:
    2019-07-02_12-43-44.jpg
  5. Use this formula to calculate the # of characters in the tab name:
    2019-07-02_12-45-35.jpg
  6. Click the filter button on the new column and select "Number filters", "Equals...":
    2019-07-02_12-46-40.jpg
  7. Set it to 3:
    2019-07-02_12-47-12.jpg
  8. Select the first two columns by control-clicking their headers and right-click. Select "Remove other columns":
    2019-07-02_12-49-37.jpg
  9. Click the expander icon next to Data:
    2019-07-02_12-51-18.jpg
  10. Keep all columns selected and OK the dialog.
  11. Delete all columns you do not need
  12. Remove the first 15 rows:
    2019-07-02_12-54-14.jpg
    2019-07-02_12-55-08.jpg
  13. Click "Use first row as headers":
    2019-07-02_12-56-16.jpg
  14. Rename first column back to "Name":
    2019-07-02_12-57-35.jpg
  15. Click "Close and Load", Close and load to...:
    2019-07-02_12-58-40.jpg
  16. Choose these settings:
    2019-07-02_13-00-21.jpg
  17. Now you're ready to create a pivot table which looks like your current report:
  18. 2019-07-02_13-03-35.jpg

Hi@Jan Karel Pieterse 

 

Thank you very much for the step by step instructions. I have been able to create a pivot table however it is slightly different to yours. For the "Statistics based on current Year" is this a named range created in the query? My attempt thus far is showing data from my first tabs of data, which i do not need to include.

 

I have attached what i am working on, the pivot table is located on row 125 down of the "Summary of Stocks" tab. Can you please see if you can tell what i need to amend in the query?

 

Thank you again for your support, much appreciated.

Looks like you at least did not filter the calculated column containing the length of the worksheet names to 3 characters. Also you didn't remove the unneeded columns. The steps are there in my instructions :-)
Related Conversations
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
*Updated 9/3* Syncing in Microsoft Edge Preview Channels
Elliot Kirk in Articles on
201 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
2 Replies