?
Solved

Add up Totals from a list

Posted on 2012-08-11
6
Medium Priority
?
330 Views
Last Modified: 2012-08-13
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
0
Comment
Question by:gisvpn
6 Comments
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38283815
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
 

Author Comment

by:gisvpn
ID: 38284133
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38284166
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 24

Expert Comment

by:Steve
ID: 38284284
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
 
LVL 24

Accepted Solution

by:
Steve earned 2000 total points
ID: 38284292
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
 
LVL 34

Expert Comment

by:Rob Henson
ID: 38287070
Or you could set up a pivot table.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question