PDA

View Full Version : [SOLVED] Excel Formula to Link Totals from Newly Created Tabs to Summary Sheet



JackChang
01-31-2020, 03:14 PM
All-

Is there a way to link my PO/invoice totals from a newly created tab to my summary sheet? without adding each formula manually?

The idea was, there is a summary tab containing the totals of the PO/invoice amounts for each project. Behind that tab, there is a template tab, which the sales reps are supposed to create a copy of for each new project. But, each time a user creates a copy of the template, they would have to add the formulas to the summary sheet. Is there anyway this can be formula driven without doing anything fancy? Or is my only option is to create a number of tabs in advance and link them up first? I'm asking because we could have 30 active projects at a time, and that's a lot of manual work.

Any suggestions appreciated, thank you!

p45cal
02-01-2020, 04:09 AM
without doing anything fancy?I can't think of one, so here's a fancy offering.
In the attached, a macro which is executed on the click of the button on the Summary sheet near cell H1.
It clears the columns A:F below row 4 (your headers) and puts formulae in.
It runs through all the sheets in the workbook, and looks at each; if it's called "Template" it's ignored, if it doesn't have "Customer #" in cell A3, it's ignored too, otherwise a new row is added to your summary sheet with formulae.
You need to be aware that:
1. Because it clears columns A:F anything you've put in there manually will be lost or overwritten
2. The row in the Summary sheet on which a given job appears depends on the order of the job sheets in the workbook, so the same row may not remain with the same job, which means that any comments you've added outside columns A:F about a job may no longer pertain to the same job.

The code's in the attachment but here it is anyway:
Sub blah()
With ThisWorkbook.Sheets("Summary")
Set Destn = .Range("A4") 'one row above where new formulae will go.
.Range("A5:F" & Rows.Count).ClearContents 'clear out old data.
End With
For Each sht In ThisWorkbook.Worksheets
With sht
If .Range("A3").Value = "Customer #" And .Name <> "Template" Then 'process only the job sheets
Set Destn = Destn.Offset(1)
FormulaStart = "='" & .Name & "'!"
Destn.Formula = FormulaStart & "$B$4"
Destn.Offset(, 1).Formula = FormulaStart & "$B$2"
Destn.Offset(, 2).Formula = FormulaStart & "$C$7"
Destn.Offset(, 3).Formula = FormulaStart & "$I$7"
Destn.Offset(, 4).Formula = FormulaStart & "$L$7"
Destn.Offset(, 5).FormulaR1C1 = "=RC3-RC4"
End If
End With
Next sht
End Sub