+ Reply to Thread
Results 1 to 6 of 6

INDEX MATCH and INDIRECT to pull in data from multiple worksheets

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    Washington DC
    MS-Off Ver
    2010
    Posts
    1

    INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    Good afternoon!

    I've been searching for several hours but can't seem to find the exact answer that will work for me. I have a workbook that contains yearly forecasts for various categories of products -- each is contained in a separate tab (22 worksheets in total). All worksheets are structured in the same fashion - data ranges have been made the same.

    I have a summary tab where I would like to pull in these final forecasts by product number and year.

    I've been able to pull in the results if I know what sheet the data is on via the following:

    =INDEX(Toys!$Y$5:$AE$150,MATCH($A24,Toys!$A$5:$A$150,0),MATCH(H$5,Toys!$Y$2:$AE$2,0))

    What I would like to do is have the formula look through the tabs so I do not need to specify where to look for each product. I think this means I need to use the INDIRECT function. I've created a list of the 22 worksheet names in in AZ2:AZ23 of the summary tab. I've tried various versions of the below but can't seem to get it too work.

    =INDEX(INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$Y$5:$AE$150"),MATCH($A24,INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$A$5:$A$150"),0),MATCH(H$5,INDIRECT("'"&$AZ$2:$AZ$23&"'!"&"$Y$2:$AE$2"),0))


    Any thoughts on what I'm missing or is there a simpler way?

    Thanks in advance for any advice!

  2. #2
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    Welcome to the forum. Post a sample workbook and we can help.
    Please ensure you mark your thread as Solved once it is. Click here to see how.
    If a post helps, please don't forget to add to our reputation by clicking the star icon in the bottom left-hand corner of a post.

  3. #3
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    Pivot Table with multiple "sources".

    http://office.microsoft.com/en-001/e...010226585.aspx
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  4. #4
    Forum Expert
    Join Date
    10-09-2012
    Location
    Dallas, Texas
    MS-Off Ver
    MO 2010 & 2013
    Posts
    3,049

    Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    If it were me, I wouldnt bother creating some slick complex indirect function, I would aggregate the data and then have ONE pivot off ONE source. It makes things so much easier.

  5. #5
    Registered User
    Join Date
    04-16-2015
    Location
    Fortaleza, Brazil
    MS-Off Ver
    Home and Student 2013
    Posts
    4

    Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    Can you explain what you mean by aggregate and then pivot off one?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: INDEX MATCH and INDIRECT to pull in data from multiple worksheets

    He means copy all the separate sheets into one sheet to serve as a database, from there it is breezy to create a Pivot Table or Pivot Report or Pivot Chart off the single data set.

+ 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. using indirect and index/match across worksheets
    By wangstar in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-02-2014, 08:32 AM
  4. Finding Max value using Index and Match data across Multiple Worksheets
    By Btray90 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 04-21-2013, 02:27 PM
  5. Replies: 5
    Last Post: 01-05-2011, 03:13 PM

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