Data quantity calculations & summary across multiple tabs

[jonesy4000]

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.

 

[mumps]

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]

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]

Try this macro:

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]

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]

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]

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]

Click here for your file.

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

 

[jonesy4000]

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]

Insert this line of code:

desWS.UsedRange.Offset(9, 0).ClearContents

below this line:

Set desWS = Sheets("Material Summary")