Results 1 to 7 of 7

Thread: retrieve data from list, enter new data

  1. #1

    retrieve data from list, enter new data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello, and thank you for viewing. Just been assigned the following monthly report.
    I have a worksheet with the following categories in columns: employee ID#, employee name, Department, position, test score last month, test score current month.
    I need to input the test score for each of 500 employees every month. The current method is to CTRL F to find the employee name or ID and then input the score. Without using a macro, what formula could be used where to accomplish the following:
    Step 1: an employee ID is entered in cell A1.
    Step 2: the entire row of data relating to that employee is returned on row 2.
    Step 3: the test score is entered in the relevant cell, say B6.
    Step 4; enter the next employee ID in A1.
    I've tried vlookup, index, match, etc. But my problem is entering the data and easily moving on to the next employee without deleting the formula when entering the score.

    Example of current worksheet:
    ID. Name Position Feb
    2015
    current month score
    1 210020 James Franco Personal Assistant to Genral Manager 16 20
    2 213172 Allison Moriarty Driver 15 9
    3 214067 Franklin Pierce Secretary to Hotel Manager 16 11
    4 214135 John 'Joe' Johnston Messenger 16 20

    Thank you!

  2. #2
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    If the data is to be returned in row 2, how does the next relevant cell become B6?

    ARe you hoping to write this new value back to the original data? I assume you are attempting this to manage the input, but wouldn't it be better to change the process, get the test scores delivered in the ID order, then it is a simple typing exercise.

  3. #3
    B2 my apologies...
    The testing process takes about two weeks every month, 500 employees. The report is broadcast everyday (during the testing period) to show which departments are lagging in getting their staff to take the test. Let's say I receive 50 tests a day, I have to grade them and add them to the report. I'm creating the report page to grab data from the data page which is the subject of this topic. Your solution would mean waiting till all the tests are complete, which wouldn't work. I could create a simple typing exercise, but I'm trying to learn advanced excel formulas to save me from simple typing exercises. So you know, the test is about product knowledge, and it's purpose is to make sure all staff are familiar the latest developments, promotions, and company knowledge. It takes the staff about 10 minutes to complete on their lunch break, but it's a major PITA for me.
    Yes, write new value back to original data. I could filter the information by employee ID, and type in the new value, but I'm looking for something easier. Earlier I said I was opposed to a Macro, but I've changed my mind, and I'm open to macros
    Last edited by FreeOT; 2015-04-07 at 11:07 AM.

  4. #4
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    All you would need in B2 would be

    =VLOOKUP($A2,'Test Details'!$A:$E,COLUMN(),FALSE)


    assuming that the data is on a sheet called 'Test Details', and just copy that across.

    You would need code to write it back. Would you do one at a time, enter the id in A2, change the score, and so on, or put all of the available ids in A2:An, and then do their scores as a batch? If the former, I would suggest event code that monitors column E, and when you change that it automatically writes it back. If the latter, you could have a button to trigger the write-back, but I would probably use the same event code approach myself.

  5. #5
    Sorry, but I don't follow. Here's an example of what I'm hoping to achieve. Thank you.
    Attached Files Attached Files

  6. #6
    Super Moderator Bob Phillips's Avatar
    Join Date
    Mar 2011
    Posts
    1,823
    Articles
    0
    Excel Version
    O365
    You didn't mention the number column. That makes the formula I gave invalid.

    This is the sort of thing I mean
    Attached Files Attached Files

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    You didn't mention the number column. That makes the formula I gave invalid.

    This is the sort of thing I mean
    That's a sweet piece of VBA, nice work, it's perfect!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •