Results 1 to 5 of 5

Index Match to get Information from another worksheet

Threaded View

  1. #1
    Forum Contributor 00Able's Avatar
    Join Date
    11-17-2010
    Location
    Pittsburgh, PA
    MS-Off Ver
    Excel 2003, 2007, 2010
    Posts
    244

    Unhappy Index Match to get Information from another worksheet

    I have a workbook with 5 worksheets, 1 an employee name list, and 4 others with an identical layout, the only difference is the cells mean something else (hours, name, production, average)but each cell directly relate to the identical cell in the other worksheets based upon column A "date". I want to pull the "line and zone" that the employee worked on based upon what date is selected in D2 of the "Data" tab.
    More Detail:
    Ok lets say I have 4 Lines of Production named Line A, Line B, Line C, Line D and on each line I have 5 workers (each in a Zone) Zone 1, Zone 2, Zone 3, Zone 4, Zone 5. I have 4 worksheets all layed out the same, with the Line and Zones going horizontally, and each date going vertically.
    In the "Avg" Tab, Line C4 tells you what the average production was done on Line A, Zone 1 on 1/1/2011
    In the "Production" Tab, Line C4 tells you what the total production was done on Line A, Zone 1on 1/1/2011
    In the "Hours" Tab, Line C4 tells you the total hours worked on Line A, Zone 1on 1/1/2011
    In the "Name" Tab, Line C4 tells you who worked on Line A, Zone 1on 1/1/2011

    So you can see, in each worksheet cell C4 is directly related to each other. I would like to retrieve a collection of this information in another worksheet called "Data" based on inputting a date into the worksheet.

    If you view the attachment, you will see I have a "sample" tab of what I would like done, based on the given information. I have highlighted the cells in each workbook, to help visualize what is happening and how the information is directly related. Each color represents an employee, so we can see where that employee worked.

    I have created another tab "Data" to show how I am pulling the other information based upon the dates that I input (thanks to ConneXionLost's help)

    I would like to return infomation specific to the "line and zone" worked, as shown in the "Sample" tab. I tried using the same equation but changing the index to reflect the row where the "Line" number would be displayed, but I get a #REF error....

    So here is my problems:
    1. The "Line" names are merged between 5 columns (representing each zone), so when I try to index match, it will only return the value for the first zone in each Line, not sure how to fix this, without calling each column its name and hiding a row, is there another way?
    2. When changing the date, the Zone fields return all #REF errors, I have no idea what is causing this as the other fields are working fine

    Any suggestions?
    Last edited by 00Able; 01-09-2011 at 03:14 PM.
    Providing Problems for Your Solutions
    STARS are my Punching Bag, You will be rewarded.

    In the rare event that I may help you, feel free to make me see STARS

Thread Information

Users Browsing this Thread

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

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