+ Reply to Thread
Results 1 to 13 of 13

index match with indirect across multiple sheets

  1. #1
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    index match with indirect across multiple sheets

    i have a workbook w/ 9 data tabs and an collaboration tab. What i am trying to do is pull the data from the 9 data tabs and insert the data on the collaboration tab. the data tabs are named by a group of people but the columns are all named the same in each tab (just different people in each). I have been trying without success to get this to work. I tried both index match and vlookup with the indirect function but keep getting errors. i have created a named range to list out all the tab names and used in the indirect function but i don't know what i am doing wrong. below are both the vlookup as well as the index match formulas i have tried. ANY feedback would be great!

    =INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))
    =VLOOKUP(A4,indirect("'"&tabs&"'!$A$3:$Y$17"),2,FALSE)

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    Quote Originally Posted by mamachrissy1028 View Post

    =INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))

    =VLOOKUP(A4,indirect("'"&tabs&"'!$A$3:$Y$17"),2,FALSE)
    If "tabs" refers to a range of cells then you'd have to select an array of cells equal to the size of "tabs" then array enter the formula.

    Array formulas need to be entered using the key
    combination of CTRL,SHIFT,ENTER (not just ENTER).
    Hold down both the CTRL key and the SHIFT key
    then hit ENTER.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: index match with indirect across multiple sheets

    I have all of my tabs listed out in a table to reference the names of the tabs i want it to search for the data. this is the named range "'"&tabs&"' that i have used in the formula. the ranges that i have specified later in the formula is the array that i want to index and search for the data that i want to pull. I don't understand why this would be wrong.

  4. #4
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    Because you're referencing an ARRAY of sheet names so the formula is doing an ARRAY of lookups and it can't return an ARRAY of lookups to a single cell.

    Instead of doing this...

    =INDEX(INDIRECT("'"&tabs&"'!a1:y100"),MATCH(A3,INDIRECT("'"&tabs&"'!a1:a100"),0),MATCH(B1,INDIRECT("'"&tabs&"'!a1:y1"),0))

    =VLOOKUP(A4,indirect("'"&tabs&"'!$A$3:$Y$17"),2,FALSE)

    "tabs" should refer to a single sheet name.

  5. #5
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: index match with indirect across multiple sheets

    OK, so then how do i look for the data on multiple sheets and return the value i am looking for? I thought that the indirect function would look for the name across several sheets and return the value where the row and column intersect on the sheet that contained the name.

  6. #6
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    Will the name appear on all the sheets, just a single sheet or multiple sheets?

  7. #7
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: index match with indirect across multiple sheets

    The names are set up in groups based on their specialty. It will appear on single or multiple sheets but not all depending if their specialty shifts at any time. However, the columns it is looking up will have empty cells if they are no longer a part of that group.

  8. #8
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    I'm totally confused!

    I would need to see the file to figure out what you're trying to do. From the sound of it, it's probably a big file.

  9. #9
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: index match with indirect across multiple sheets

    Here is an example of what i am working on. The tasked seemed simple enough, but i don't know what i am doing wrong....

    test.2.xlsx

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    Since the data on the Dept sheets is numeric it looks like what you want to do is a SUMIF across the sheets?

  11. #11
    Registered User
    Join Date
    03-08-2015
    Location
    cleveland, ohio
    MS-Off Ver
    2013
    Posts
    48

    Re: index match with indirect across multiple sheets

    I don't want to sumif b/c each agent can only be counted once per column as they can only have 1 specialty. If for some reason there is an error i don't want there to be a 2 but to rather throw an error.

  12. #12
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,256

    Re: index match with indirect across multiple sheets

    1st, you actually dont have (that I can find) a table or range name with all tab names, so I made 1 on Cheats sheet, and called it Depts. Just a simple table with Dept 1 - Dept 9, then gave it the range name of Depts

    See if this will help you...
    =SUMPRODUCT(SUMIF(INDIRECT("'"&Depts&"'!A3:A100"),$A3,INDIRECT("'"&Depts&"'!"&CHAR(97+COLUMN()-1)&"3:"&CHAR(97+COLUMN()-1)&"100")))
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  13. #13
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: index match with indirect across multiple sheets

    I have no idea what result you expect!

+ 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. Replies: 7
    Last Post: 01-10-2017, 11:11 PM
  2. [SOLVED] Extracting information from multiple tables (Vlookup, Index match, indirect??)
    By vesper007 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-07-2016, 02:36 AM
  3. [SOLVED] Using INDEX, MATCH, and INDIRECT to reference cells in other sheets
    By jgilbe31 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 07-14-2015, 02:02 PM
  4. Replies: 1
    Last Post: 06-18-2015, 08:45 AM
  5. INDEX MATCH and INDIRECT to pull in data from multiple worksheets
    By nebshaver123 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 04-16-2015, 03:58 PM
  6. Replies: 8
    Last Post: 10-14-2014, 01:54 AM
  7. [SOLVED] INDIRECT Function with multiple sheets - SUMIF, INDIRECT & MATCH
    By DJDRU in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-07-2014, 08:42 AM

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