Data quantity calculations & summary across multiple tabs

jonesy4000

New Member
Joined
Jun 6, 2019
Messages
6
I have a workbook for budgeting construction projects. Each element of a project gets its own tab (for example, walls, roof, floor, etc.). Within each of those tabs the user chooses a category of material from a drop down menu, then the actual material from a second drop down menu. Those drop downs reference a sheet with tables for each category. So, for example, tab "1" would be for the roof. In the first column they could choose "Lumber" which allows them in the second column to choose "2x8x8' #2 YP." Then, they manually input quantities of the material. And so it goes until all the necessary materials for each element are accounted for. Then, the process is repeated on the next tab for the next element.

Obviously, many materials are repeated in multiple tabs. For quick ordering purposes, I'd like to create a "Material Summary" tab that looks at all the element tabs, lists the materials chosen, and creates a sum of those materials from across all the tabs. There are hundreds of possible materials to choose from in the page of material tables, so the summary should only list materials that are actually chosen, and not a list of all possible materials. IDEALLY, it would group the materials by category, but that's not totally necessary.

I'm at a total loss for how to do this. Any help is appreciated.
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
I think that it would be easier to help and test possible solutions if I could work with your actual file which includes any macros you are currently using. Perhaps you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do using a few examples from your data and referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.
 

jonesy4000

New Member
Joined
Jun 6, 2019
Messages
6
Can do! The file is on dropbox here. The relevant tabs are the numbered ones in the middle. I have filled in a few values in Tabs 1,2, & 3 as an example. I also created a "Material Summary" tab as a starting point for where this info should go, and manually filled in the data to show what I'd like the formula to eventually do.

Example:
Tab 1; Cell B5 is "BC Plywood - 1/4"x4x8" <----This is a user created value from a drop down menu, based on the category picked in A5
Tab 1; Cell C5 is "3" <----This is also a user created value of the quantity needed from B5
Tab 1; Cell D5 is "ea" <----This is the unit of measurement of the item listed in B5
Tab 1; Cells B6-D6 is "1x4 Poplar" "22" "Ln Ft"<----Same idea as above,
Tab 1; Cell B7-D7 is "1" SST 16 ga" "40" LFT<----Same idea as above

This trend continues in Tab 2 & Tab 3, and eventually across all numbered tabs.

Tab "Material Summary"; Cell A10 is "BC Plywood - 1/4"x4x8" <---This value should be generated automatically by searching the relevant tabs, finding this name, and inserting it here.
Tab "Material Summary"; Cell B10 is "9" <---This value should be generated by searching all relevant tabs for "BC Plywood - 1/4"x4x8" and creating a sum of all instances and their quantities.
Tab "Material Summary"; Cell C10 is "ea" <----This is the unit of measurement of "BC Plywood - 1/4"x4x8"
Tab "Material Summary"; Cells A11-C15 <--- continues the calculations as above, but with the rest of the values from column B in each tab.

Hopefully this makes some kind of sense. You should be able to figure out from the spreadsheet what I'm going for.
Thanks a lot!
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Try this macro:
Code:
Sub MatSum()
    Application.ScreenUpdating = False
    Dim LastRow As Long, ws As Worksheet, x As Long, mat As Range, fnd  As Range, desWS As Worksheet
    Set desWS = Sheets("Material Summary")
    For x = 1 To 30
        With Sheets(CStr(x))
            LastRow = .Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each mat In .Range("B5:B" & LastRow)
                Set fnd = desWS.Range("A:A").Find(mat, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    fnd.Offset(, 2) = mat.Offset(, 2)
                    fnd.Offset(, 1) = fnd.Offset(, 1) + mat.Offset(, 1)
                Else
                    desWS.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 3) = mat.Resize(, 3).Value
                End If
            Next mat
        End With
    Next x
    Application.ScreenUpdating = True
End Sub
If you want to group the materials by category, you would need add a "Category" column to the "Material Summary" sheet. Please let me know if that is what you want.
 

jonesy4000

New Member
Joined
Jun 6, 2019
Messages
6

ADVERTISEMENT

Thanks a lot! Grouping materials by category would be awesome, but it's a lower priority.

I can get it to ALMOST work. Here's the link to my updated file. I inserted the macro into a button called "Summarize" on the Material Summary page. Maybe that wasn't a good thing to do?

It seems like it's calculating everything just fine as long as the materials were generated on the tab BEFORE I inserted the macro. I've added an item to tabs 1 & 3, run the macro again, and it doesn't update to reflect the changes.

Also, what is the "27" in cell B8?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,203
Office Version
  1. 365
Platform
  1. Windows
Cross posted https://www.excelforum.com/excel-ge...terials-across-multiple-tabs.html#post5133436

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.
 

jonesy4000

New Member
Joined
Jun 6, 2019
Messages
6

ADVERTISEMENT

I'm sorry about that. I missed the regulation, and totally understand why it exists.

This question is also posted at Excel Forum here.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Click here for your file.
Code:
Private Sub CommandButtom1_Click()
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim LastRow As Long, ws As Worksheet, x As Long, mat As Range, fnd  As Range, desWS As Worksheet
    Set desWS = Sheets("Material Summary")
    For x = 1 To 30
        With Sheets(CStr(x))
            LastRow = .Range("B:B").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            For Each mat In .Range("B5:B" & LastRow)
                Set fnd = desWS.Range("A:A").Find(mat, LookIn:=xlValues, lookat:=xlWhole)
                If Not fnd Is Nothing Then
                    fnd.Offset(, 2) = mat.Offset(, 2)
                    fnd.Offset(, 1) = fnd.Offset(, 1) + mat.Offset(, 1)
                Else
                    desWS.Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Resize(, 3) = mat.Resize(, 3).Value
                End If
            Next mat
        End With
    Next x
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub
 
Last edited:

jonesy4000

New Member
Joined
Jun 6, 2019
Messages
6
Thanks, mumps. That works better. However, now every time I press the summarize button, it recounts everything and adds the new total to the previous total. Any way to fix that?
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,152
Insert this line of code:
Code:
desWS.UsedRange.Offset(9, 0).ClearContents
below this line:
Code:
Set desWS = Sheets("Material Summary")
 

Watch MrExcel Video

Forum statistics

Threads
1,123,135
Messages
5,599,917
Members
414,348
Latest member
KloppyM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top