+ Reply to Thread
Results 1 to 3 of 3

Lookup from fixed rows and dynamic columns

  1. #1
    Registered User
    Join Date
    11-08-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Lookup from fixed rows and dynamic columns

    Hi,

    I have 2 workbooks, one which contains an extract of data from a telephony system and one which I'm using to calculate agents stats from the other. I have the problem where the system creates over half the columns dynamically so they're never or very rarely in the same position each day. I need to be able to lookup the value for both the agent (based on their ID) and by column header so no matter where the data is output I can pull the information for the correct agent in the right column on my second sheet.

    I've attached a sample of information which is part of the report that I get from the telephony system so you can see the type of data I'm looking up. The problem is I'm not sure what order they'll come out each day so is it possible to do what I'm after? The calculation report is going to be looking up the value for the agent and then working out percentages for time spent in each state by team which is why I need the two spreadsheets.

    I'm sure I did this in my last job but didn't take the work with me to my new job so have completely forgot what I did. I think I used the SUMPRODUCT function in a formula but the column headers may have been static in those tables. Hope this can be done and thanks in advance.

    EDIT: Just thought the title is wrong as the rows won't be fixed as I'll be putting the agents into tables based on their teams so both row and column is dynamic if it makes a difference.
    Attached Files Attached Files
    Last edited by Alexvs; 11-08-2010 at 11:26 AM. Reason: Added extra info

  2. #2
    Registered User
    Join Date
    10-06-2009
    Location
    Adelaide, Australia
    MS-Off Ver
    Excel 2007
    Posts
    40

    Re: Lookup from fixed rows and dynamic columns

    Is this what you mean - using INDEX MATCH combination?
    Attached Files Attached Files

  3. #3
    Registered User
    Join Date
    11-08-2010
    Location
    England
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: Lookup from fixed rows and dynamic columns

    It looks like it could work but doesn't give me the right information in the right columns as I want the data for AGENTTIME in the corresponding column and so on. Does MATCH work on column numbers or does it match the string in that cell? I need it to match the text due to the columns changing place in the sample worksheet.

    I should've also explained the sample worksheet better, the seperate numbered tabs are the days e.g. 1st, 2nd, 3rd etc of the month and the second worksheet will be the one that will have the formulas looking up the data in similar numbered tabs. I've attached both of the workbooks this time so you can see the result I'm looking for.

    I tried following the formula that you put in the attached file and it had the wrong data in the wrong columns which is the same as what's happened in the example you attached.

    Thanks

    EDIT: Ignore this after some tweaking I managed to get it working I just had to edit the cells it was starting at in order for it to select the right one. So this is now solved. Thanks
    Attached Files Attached Files
    Last edited by Alexvs; 11-09-2010 at 06:41 AM. Reason: Solved

+ 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