Data auto summing

[taichi56]

I have a workbook that has about 250 rows of student information. The columns are as follows:

LastName
FirstName
StudentId
SpringHours
FallHours

The column I am interested in is the FallHours. Right now our data clerk has to input hours that students have made up. So she has to use the Find field, type in the StudentID and then input the hours in that students cell. If there are already hours in that cell she then has to total the hours she is inputting.

Is there any coding where the data clerk could just type in the StudentID and the Fallhours on a seperate worksheet. Then have the coding check the master document by StudentID and when it finds a match, it will input the new hours and total the hours if that cell already had a number in it? It sure would save us hours of manual labor. Thank you, hope someone can help.

[Richard Buttrey]

Hi, and welcome to the Forum.

This is a frequent requirement in most applications, and the =VLOOKUP() function is the one you need.

1. Create a new Sheet2, with Student ID in column A and hours in column B.
2. On sheet1 copy column E, the Fall Hours and paste the values in say column F.
3. Now in the first cell in column E - let's say E2 enter the following formula:

Please Login or Register  to view this content.

then copy this down all rows.

The next time you have some more data to enter, just make sure that before you enter the new data on sheet 2, that you've copied Column E (step 2 above), and used the Edit PasteSpecial Values to copy the formula results as values in column F. Then as you add the new data on sheet2 the sheet1 totals in column E will automatically accumulate.

If this is a regular process then it would make sense to automate the copy/paste with a macro. But I suggest you become familiar with the process first, then when you're ready just let us know and we'll come up with a simple macro.

HTH

[taichi56]

It worked perfectly. Thank you very much. My data entry clerk will be very happy. I can probably record the Macro I need. I will ask in a while if that does not work. Again thank you.