+ Reply to Thread
Results 1 to 2 of 2

INDEX MATCH across multiple workbooks and worksheet including INDIRECT functions

  1. #1
    Registered User
    Join Date
    09-20-2017
    Location
    Nashville, TN
    MS-Off Ver
    2016
    Posts
    2

    INDEX MATCH across multiple workbooks and worksheet including INDIRECT functions

    I have been trying in vain to create a dashboard based on numbers from other published monthly Excel leaderboards for our team.
    I don't have access to the raw data from which the leaderboards are derived, but I can see all of the rendered and interpreted data as it relates to our team's performance metrics.
    So far, I have been attempting to use the below formula, but am getting a "There's a problem with this formula" error that I am still unable to resolve:

    =INDEX(" ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx]" & D$2 & " ' !$B$5:$V$32",
    MATCH("OUR TEAM"," ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx] " & D$2 & " ' !$B$5:$B$32" ,0),
    MATCH(E$3," ' http://intranetlocation/[ " & $C4 & "_" & D$4 "_LEADERBOARD.xlsx] " & D$2 & " ' !$B$5:$V$5" ,0))

    where

    C column = reference year of leaderboard workbook title
    D column = reference month of leaderboard workbook title
    row 2 = helper line for tab name(s) in outside workbook(s)
    row 3 = column with desired data

    The bold section is where Excel is highlighting my error.

    What am I missing in this syntax?

    Is this too complicated with outside book and tab name references to employ for a single formula??

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,420

    Re: INDEX MATCH across multiple workbooks and worksheet including INDIRECT functions

    Hi

    Perhaps an & after the $D$4 ?

    Otherwise upload the two workbook
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ 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. MACRO code for an index match functions to fetch from multiple open workbooks
    By Pawann in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-01-2017, 10:18 AM
  2. vba code for an index match functions across different workbooks - all open
    By Pawann in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-01-2017, 02:22 AM
  3. Replies: 1
    Last Post: 07-31-2017, 07:03 PM
  4. [SOLVED] Index Match with multiple criteria including date range
    By Perk1961 in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 07-11-2017, 08:23 PM
  5. index match with indirect across multiple sheets
    By mamachrissy1028 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-13-2015, 08:43 PM
  6. Sumif or Index/Match on Multiple Criteria including Dates to Month?
    By patrick1024 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-19-2015, 11:00 PM
  7. Combine INDEX+MATCH functions with INDIRECT formula
    By ABSTRAKTUS in forum Excel General
    Replies: 4
    Last Post: 04-12-2011, 04:16 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