Add up Totals from a list

Hello,

I have a spreadsheet as attached.

On Sheet 1 I have names listed from B13 downwards - these names change day to day - there may be 5 or 40 however each name on this list always remains unique.

On Sheet 2 I have the names (the same names as listed on Sheet 1 but on many rows) in column F. On the same row as each name in column J  I have a number.


I would like to, via VBA, go down the list of names as shown on sheet1 from B13 down (until there are no more names) and against each name in C13, 14 etc.. put a total.

The total would be a sum of column J (from sheet2) where that name appears in column (from sheet2) and shown in a total on Sheet 1 in B13,14 etc for each name.

Sorry If this is not clear, please let me know if I need to help explain it any further ;)

Thanks,

GISVPN
Example-1.xlsx
gisvpnAsked:
Who is Participating?
 
SteveCommented:
Following code clears rows 13-999 before re-entering the names and adds a static sum.

Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")

ws.Rows("13:999").Delete
x = 13
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    ws.Cells(x, 3) = WorksheetFunction.SumIf(ws2.Range("F:F"), ws.Cells(x, 2), ws2.Range("J:J"))
    x = x + 1
End If
y = y + 1
Loop
End Sub

Open in new window

All depends on how you want the file to work.
Example-1.xlsm
0
 
Saqib Husain, SyedEngineerCommented:
Try

Sub subtotals()
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).FormulaR1C1 = "=sumif(sheet2!c6,rc[-1],sheet2!c10)"
Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value = Range("C13", Range("B" & Rows.Count).End(xlUp).Offset(, 1)).Value
End Sub

Open in new window

0
 
gisvpnAuthor Commented:
That does not seem to work.

I get a prompt to save the workbook each time I run the macro.

Can I specify in the code this is to look only at C13 downwards for any names in B13 downwards only on the Summary tab of the workbook.
I like the idea of a formula being used; )
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
Saqib Husain, SyedEngineerCommented:
You ought to be running the code from the summary sheet and also make sure that the name of the data sheet is sheet2. If the data sheet has some other name then change all instances of sheet2 in the formula to whatever name you have.

Have you tried the macro on the file you uploaded?
0
 
SteveCommented:
Attached is a file which puts the sumif formula in the cell C13 onward.
this is after creating list with hyperlinks to sheets.

See attached:
Sub DataNamesMake()
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.Sheets("Summary")
Set ws2 = wb.Sheets("data")
x = ws.Cells(Rows.Count, 2).End(xlUp).Row + 1
y = 1

Do While Len(ws2.Cells(y, 6)) <> 0
If WorksheetFunction.CountIf(ws.Range("B:B"), ws2.Cells(y, 6).Value) = 0 Then
    ws.Hyperlinks.Add Anchor:=ws.Cells(x, 2), Address:="", SubAddress:=ws2.Cells(y, 6).Value & "!A1", TextToDisplay:=ws2.Cells(y, 6).Value
    ws.Cells(x, 3).FormulaR1C1 = "=SUMIF(Data!C[3],RC[-1],Data!C[7])"
    x = x + 1
End If
y = y + 1
Loop
End Sub

Open in new window

Example-1.xlsm
0
 
Rob HensonFinance AnalystCommented:
Or you could set up a pivot table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.