+ Reply to Thread
Results 1 to 2 of 2

Excel 2007 : Index and Match using multiple worksheets

  1. #1
    Registered User
    Join Date
    10-27-2011
    Location
    Toronto
    MS-Off Ver
    Excel 2003
    Posts
    3

    Index and Match using multiple worksheets

    Attached is a sample of a workbook I am trying to get totals for. I tried using Index and Match but keep getting an error message saying there is too many criteria. I want to be able to have column F on the sheet 'Reports' bring up a total for whatever criteria is selected from the drop downs in Columns A-E (pulling data from the first few worksheets). Does anyone know how to do this?

    Thanks!
    Attached Files Attached Files

  2. #2
    Forum Expert davegugg's Avatar
    Join Date
    12-18-2008
    Location
    WI, US
    MS-Off Ver
    2010
    Posts
    1,884

    Re: Index and Match using multiple worksheets

    The INDEX() MATCH() function combination you are refering to is designed to be used on a single table. I suppose it could be used on multiple tables, but they would need to be in the same basic format with the same data in each row (which is probably never realistic). If you want to use it (and for general ease of use), you need to rearrange your data into a single table with a column for each field.

    Lose the month name, just use one column for month and put the names in that column. Put the company number in one column, the employee name in one column the detail in one column and the expense in one column. You'll find your table gets MUCH longer, but the data will be much easier to work with. Once you have that data in a single table, you can use formulas to fill in any sheet that you want to use to display that data.

    For more info on the best ways to set up data, check out the link in my signature.
    Is your code running too slowly?
    Does your workbook or database have a bunch of duplicate pieces of data?
    Have a look at this article to learn the best ways to set up your projects.
    It will save both time and effort in the long run!


    Dave

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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