+ Reply to Thread
Results 1 to 7 of 7

Retrieving Cell Data and Reverse Lookup in separate spreadsheet

  1. #1
    Registered User
    Join Date
    11-30-2011
    Location
    Riyadh
    MS-Off Ver
    Excel 2010
    Posts
    3

    Unhappy Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Hello Everyone,
    My name is Melvin and I work as an accountant in a Logistics Company. I am a newbie here and I implore your help in a major predicament with Microsoft Excel.

    Let me begin,

    Lets say I have 2 spreadsheets.

    1. The first document, SALEXCEL_1111 contains a large list of salary values of individuals of different pay grades. The Left Column is PAY GRADES namely with a letter and a number. For example, A1, A2 upto A10, subsequently B1,B2 upto B10. I hope to bring to your attention that these values are not cell references but the actual data within the cells.
    And the lower row is that of SENIORITY in years.
    _______
    A1
    A2
    ..
    A10
    _______
    B1
    B2
    ..
    B10 | 2250 | 2500 | 2750 |
    _______

    SENIORITY | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 |



    2. The second document, SALEXCEL_2222 is that of a specific company branch office with employee details such as the name field, position and then PAY GRADE.

    NAME | POSITION | PAYGRADE | SENIORITY | SALARY

    XYZ | Accountant | B10 | 2 | XVALUE


    Now the biggest challenge is that, there are different employees with different pay grades and many such branch office spreadsheets. Here thankfully, the main database spreadsheet is a constant.
    Is there any way to obtain XVALUE by Looking up the paygrade and the seniority cell values in SALEXCEL_2222 and obtaining the required addresses and then by referencing SALEXCEL_1111 and looking up the cell data value of the PAY GRADE column and once that is obtained in SALEXCEL_1111,
    then moving across the SENIORITY ROW to arrive at the correct [XVALUE].

    For example,
    while in SALEXCEL_2222;
    if for the person XYZ who is an accountant with the paygrade B10 and seniority of 2 years,
    then the SALARY can be obtained by looking up in SALEXCEL_1111
    and looking down at the left PAYGRADE column arriving at B10,
    then looking across the SENIORITY column arriving at 2
    with the final XVALUE being 2500.

    This is the summary of my problematic predicament.
    Any help with this will be huuuuuugely and gratefully appreciated with all my heart! :D
    Last edited by countdredd; 11-30-2011 at 05:22 AM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,960

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Hi,

    It's always easier to understand your question if we can see it in context.

    Please upload your workbook, or at least a representative sample, with notes referring to the cells involved and manually add the results you expect to see.

    Anonymise the data since no doubt it is sensitive.

    Regards
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,831

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Please attach workbook with dummy data similar to your original format ,if possible with example what you expect to do.

  4. #4
    Registered User
    Join Date
    11-30-2011
    Location
    Riyadh
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Thank you guys for replying. Hope these attachments convey a better picture. ^_^

    ATTACHMENTS:
    SALEXCEL_1111
    http://www.mediafire.com/?ux12tbvpa84yy41

    SALEXCEL_2222
    http://www.mediafire.com/?2td00b4xtaat5ao

  5. #5
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    27,960

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Hi,

    Please upload to the forum and not an independent site whose provenance we don't know.

  6. #6
    Registered User
    Join Date
    11-30-2011
    Location
    Riyadh
    MS-Off Ver
    Excel 2010
    Posts
    3

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    I aplogise for the oversight. I hope this is adequate.
    Attached Files Attached Files

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2010
    Posts
    5,831

    Re: Retrieving Cell Data and Reverse Lookup in separate spreadsheet

    Pl see the attached file.Formulas are entered in E7 cell as array formula and dragged down.
    Your feedback and clarifications are welcome.
    Note: Both the files should be in same folder.
    Attached Files Attached Files

+ 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