+ Reply to Thread
Results 1 to 2 of 2

Index/Match across multiple Worksheets in Workbooks on Sharepoint

  1. #1
    Registered User
    Join Date
    04-15-2019
    Location
    Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    2

    Question Index/Match across multiple Worksheets in Workbooks on Sharepoint

    Hi everyone,

    I'm relatively new to the Excel world and have learning formulas on the fly - made timesheets to pull data from calendars I've made but as I started uploading everything to SharePoint and fixing all the links, I realized that I had forgotten to ensure the data pulled is not only for one month but all 12.

    I haven't a clue how to even begin with using the indirect function to be able to search from all 12 worksheets. See attached images.

    Current formula pulling for just the month of May into row 9 of Timesheets:
    =INDEX([OT_Calendar.xlsx]May!$B$4:$AI$75,MATCH($A$9,[OT_Calendar.xlsx]May!$B$4:$B$75,0),MATCH(C$8,[OT_Calendar.xlsx]May!$B$4:$AI$4,0))



    Any ideas would be much appreciated!

    Cheers,
    Win
    Attached Images Attached Images
    Last edited by winmiester; 04-15-2019 at 09:47 PM.

  2. #2
    Registered User
    Join Date
    04-15-2019
    Location
    Canada
    MS-Off Ver
    Microsoft Office Professional Plus 2013
    Posts
    2

    Re: Index/Match across multiple Worksheets in Workbooks on Sharepoint

    Figured it out! In case you're interested:

    Mad props to this amazing tutorial that really breaks down the steps: breakingintowallstreet.com/biws/advanced-formulas-in-excel-index-match-indirect/
    Well worth the half hour to watch.

    First step was to try out the indirect function to see if I could get the info from a worksheet in the SAME workbook according to the month, where C$8 was a date (e.g. 24-Jun). Worksheets were named "MMM_PT"
    =INDEX(INDIRECT("'"&TEXT(C$8,"MMM")&"_PT'!$A$5:$AJ$76"),20,15) - random row/column to look up

    Note that the worksheet names were changed to have a common denominator ("_PT"). In my earlier post, I was working on the OT sheets but switched to the PT sheets... the idea is the same though.
    [in case anyone is wondering what this bit is: "'"&TEXT
    it reads as " ' " &TEXT


    Then did match functions for the appropriate row =MATCH($A$9,Jun_PT!$B$5:$B$61,0) and column =MATCH(C$8,Jun_PT!$F$5:$AJ$5,0)

    Substituted the match functions and checked if each worked in the index/indirect formula. If so, took the indirect formula and subbed in for the worksheet/array.
    =INDEX(INDIRECT("'"&TEXT(C$8,"MMM")&"_PT'!$A$5:$AJ$76"),MATCH($A$9,INDIRECT("'"&TEXT(C$8,"MMM")&"_PT'!$B$5:$B$76"),0),MATCH(C$8,INDIRECT("'"&TEXT(C$8,"MMM")&"_PT'!$A$5:$AJ$5"),0))

    Last step was to try doing this with sheets from another workbook.
    =INDEX(INDIRECT("'[PT_Calendar.xlsx]"&TEXT(C$8,"MMM")&"_PT'!$A$5:$AJ$76"),MATCH($A$9,INDIRECT("'[PT_Calendar.xlsx]"&TEXT(C$8,"MMM")&"_PT'!$B$5:$B$76"),0),MATCH(C$8,INDIRECT("'[PT_Calendar.xlsx]"&TEXT(C$8,"MMM")&"_PT'!$A$5:$AJ$5"),0))

    Last edited by winmiester; 04-15-2019 at 09:49 PM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. index and match using multiple workbooks
    By pikepro92 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 02-13-2019, 09:22 AM
  2. multiple index and match using multiple workbooks and sheets.
    By TinoBradica in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-16-2018, 10:40 PM
  3. [SOLVED] Index Match with different data length and across multiple workbooks
    By Faridwahidi in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 09-22-2014, 09:03 AM
  4. Index & match SUM multiple worksheets
    By Thito in forum Excel General
    Replies: 12
    Last Post: 04-02-2014, 05:15 PM
  5. FIND & MATCH - Multiple workbooks & worksheets
    By ngocpdn in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-09-2013, 06:13 PM
  6. Index and Match across multiple worksheets
    By GTHORE in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 02-09-2013, 01:05 PM
  7. Index match multiple worksheets
    By rrivera616 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-28-2013, 07:03 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1