If cell value matches, then copy data into specific cells

[hleelor]

Hello awesome excel forum,

I'm working on a database for homeless youth to help with graduation and retention. Any direction would be appreciated it! I've been googling and searching the forum like a mad woman and haven't found quite what I am looking for.

I have one workbook with multiple sheets. I want to find the sum of how many credits a student has in the Science, Mathematics, Elective, etc and take the sum from "Database" sheet and paste it into the sheet that matches by. I have divided up the students by grade level, so I have a separate sheet for 9th, 10th, 11th, and 12th graders. For example, if the student ID number says 4005, the information for John Smith (who is a 12th grader) would be posted into the "12" sheet and sum all the credits that John Smith earned in the Sciences into cell H24.

I think VBA would be the best route, but let me know if you have any other ideas. I'm learning VBA pretty quickly, so I don't necessarily need the whole code, but if you have something to point me into the right direction, I would appreciate it!

[tigeravatar]

Hello hleelor,

Welcome to the forum!
Using your provided example file, I think a simple Sumifs formula will work for you. The example file is a .xls, but your MS-Off Version shows as 2010. If you have Excel 2003 or below, we can switch it to a Sumproduct version instead (Sumifs only works in Excel 2007 and higher).

So on your grade sheets, in this case Sheet '12', in cell F24 (the corresponding row for John Smith, ID 4005), use this formula:

Please Login or Register  to view this content.

Then copy over and down. You can use that same formula in all of your grade level sheets.

[hleelor]

Thanks, Tigeravatar! The formula worked for the cell F24 and the sum credits for the sciences was correct. However, when I copied the formula down, the sum for the other subjects weren't adding up correctly... any additional help? I'm going to continue to play with it.

Thanks in advance!

[tigeravatar]

hleelor,

It worked for me using your sample file. I'd have to see a new sample file showing where it doesn't work to find the issue.

[hleelor]

Tigeravatar,

Thanks, I'm going to play around with it a little bit more tonight to see what I come up with. I also want to talk to my colleagues to see what they think of the format! I will probably need some more help tomorrow!

[hleelor]

Tigeravatar,

Need some more help, please!

Made some minor tweaks to the spreadsheet and I would like the data to sum up a little bit differently. I would like to sum up the credits by Current Academic Year Credits vs History. I added an additional column to the "Database" spreadsheet. In Column G, it shows you what grade the student took the class. If column G matches with column D, then I want the sum to be added in the "Current Academic Year Credits" columns. If column G does not match with column D, then I want the sum of the credits to be added into the corresponding columns in the "History" columns.

How would your formula change based on the above new information?

Thanks again in advance!

[tigeravatar]

In cell F5 of the grade level sheets, use this formula for the current grade credits (change the 12 to the specified grade level, so 11 for sheet '11' etc) and copy right:

Please Login or Register  to view this content.

In cell P5 (the first history column) and copied right:

Please Login or Register  to view this content.

[hleelor]

Looking forward to it!

[tigeravatar]

Sorry for the delay, work has been busy today, i have edited the post with formulas that should work for you.

[hleelor]

Works perfectly! Thank you for helping out your fellow homeless youth in Colorado!

I "added to your reputation." I'm still pretty new to the forum, so let me know if I can do anything in return for your help.

[tigeravatar]

You're very welcome

If that takes care of your need, please mark this thread as solved.
How to mark a thread Solved
New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:
Go to the first post
Click edit
Click Go Advanced
Just below the word "Title" you will see a dropdown with the words "No prefix"
Change to "Solved"
Click Save

[hleelor]

I'm sure there is a much easier way of doing this, but I want to have data transferred from "sheet1" to respective sheets 9, 10, 11, 12 based on grade level student is in. Here is my current VBA coding but now I only want data to be transferred if it's new information.


Sub As_Of_Analysis_Sorting()
Dim lr As Long, lr2 As Long, r As Long
lr = Sheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("9").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("10").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("11").Cells(Rows.Count, "A").End(xlUp).Row
lr5 = Sheets("12").Cells(Rows.Count, "A").End(xlUp).Row
For r = lr To 2 Step -1
If Range("G" & r).Value = "9" Then
Range("C" & r).Copy Destination:=Sheets("9").Range("A" & lr2 + 1)
lr2 = Sheets("9").Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & r).Copy Destination:=Sheets("9").Range("B" & lr2)
lr2 = Sheets("9").Cells(Rows.Count, "A").End(xlUp).Row
Range("E" & r).Copy Destination:=Sheets("9").Range("C" & lr2)
lr2 = Sheets("9").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("G" & r).Value = "10" Then
Range("C" & r).Copy Destination:=Sheets("10").Range("A" & lr3 + 1)
lr3 = Sheets("10").Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & r).Copy Destination:=Sheets("10").Range("B" & lr3)
lr3 = Sheets("10").Cells(Rows.Count, "A").End(xlUp).Row
Range("E" & r).Copy Destination:=Sheets("10").Range("C" & lr3)
lr3 = Sheets("10").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("G" & r).Value = "11" Then
Range("C" & r).Copy Destination:=Sheets("11").Range("A" & lr4 + 1)
lr4 = Sheets("11").Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & r).Copy Destination:=Sheets("11").Range("B" & lr4)
lr4 = Sheets("11").Cells(Rows.Count, "A").End(xlUp).Row
Range("E" & r).Copy Destination:=Sheets("11").Range("C" & lr4)
lr4 = Sheets("11").Cells(Rows.Count, "A").End(xlUp).Row
End If
If Range("G" & r).Value = "12" Then
Range("C" & r).Copy Destination:=Sheets("12").Range("A" & lr5 + 1)
lr5 = Sheets("12").Cells(Rows.Count, "A").End(xlUp).Row
Range("D" & r).Copy Destination:=Sheets("12").Range("B" & lr5)
lr5 = Sheets("12").Cells(Rows.Count, "A").End(xlUp).Row
Range("E" & r).Copy Destination:=Sheets("12").Range("C" & lr5)
lr5 = Sheets("12").Cells(Rows.Count, "A").End(xlUp).Row
End If
Range("A1").Select
Next r
End Sub

[tigeravatar]

Give this a try:

Please Login or Register  to view this content.

[hleelor]

This is great!