Several Sheets, Several Colums
Hello everyone, i´m new to this forum, I tried my best to figure out my problem, but I gave up, so i´m here in hopes that someone could help me out. This is my scenario: I have 6 sheets, my first sheet "SUPPLIERS" looks something like this:
A B C D
ROMESA GRUFER JJCP DAVE & NORM
All other five sheets "CONSTRUCTION MATERIALS 1"...2,3...etc
DESCRIPTION SUPPLIER INVOICE # TOTAL
First cargo buy ROMESA 7523113 $875.00
First cargo buy ROMESA 7563152 $822.40
First cargo buy GRUFER 00896433 $56.00
First cargo buy ROMESA 7902134 $377.13
etc..............
Now what I want to do is the following: In my sheet "SUPPLIER" column A, B, etc. is add all values from all sheets from that specific SUPPLIER, so I need to use a function that first needs to evaluate what SUPPLIER it is (ex. =IF(Sheet!'CONSTRUCTION MATERIALS 1' = "ROMESA", --- add value from TOTAL ---, ELSE;GRUFER,JJCP etc.)
I´m not sure if my explanation is clear enough, but I hope someone can help me out with this.. any help will be hugely appreciated, thanks in advance!!!
Are you OK with the solution
Are you OK with the solution using macros or you want a solution with formulae only ?
Here is solution using macro. Paste the following code in a general module and run.
Sub SumTotal()
Dim rngSuppliers As Range
Dim rngSupplier As Range
Dim wks As Worksheet
Dim dblSum As Double
Set rngSuppliers = ThisWorkbook.Worksheets("Suppliers").Range("A1").CurrentRegion.Rows(1)
For Each rngSupplier In rngSuppliers.Cells
For Each wks In ThisWorkbook.Worksheets
If Left(UCase(wks.Name), 4) = "CONS" Then
dblSum = Application.WorksheetFunction.SumIf(wks.Range("B1").EntireColumn, rngSupplier.Value, wks.Range("D1").EntireColumn)
End If
Next wks
rngSupplier.Offset(1).Value = dblSum
Next rngSupplier
End Sub