• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Adding Multiple sheets with SUMIF formula

cassa80

New Member
Hi all

PLEASE PLEASE if anyone can help!

I am in charge of controlling all company assets leaving from and returning to the DC. There is no system in place currently, so I am using Excel 2013 to capture these movements manually.
I have created a dropdown list on my sheet1 (May), to select whichever store the assets went to and returned from. This then updates my second sheet (Summary) by using the formula:

=SUMIF(MAY!A4:A104,SUMMARY!B3,MAY!C4:C104)

This formula works perfectly as it totals up only the selected stores needed.

However, my problem comes in as I have to duplicate sheet1 (May) 30 times to complete a month. By doing this, the formulas do not recognize the other sheets.

Is there any way for me to edit this formula for it to sum all the sheets at the end? Or maybe another simpler way of doing it.

Thanks,,,, Lee
 

Attachments

  • ASSET CONTROL TEMPLATE IN WORK.xlsx
    656.1 KB · Views: 3
Hi Lee,

As you are getting ready to design the system from "scratch", so to speak, let's make sure we set things up the best way possible. WHen working in Excel, it's best to keep all your data in one central location (sheet), and then use the other sheet(s) to perform analsys on that data. If you start spreading data over multiple sheets, things get complicated, and XL doesn't work well. In the attached, I've modifed the design of your workbook. All the data now goes into a single sheet, and the sheet has an XL table. By having the Date and the Sent/Received as a column in our data, we can easily query the information you need. By using an XL table, our formulas will automatically expand and adjust as new data is added.

You can see the new formulas in your Summary sheet. for your In/Out report, rather than having to build formulas looking all over the place to figure out what the totals were, you can let XL do the work via a PivotTable. I've tried to match your previous layout.

Take a moment to review some of the "best practice" ideas here:
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

If you absolutely hate my ideas, that's fine, and we can try to come up with a solution for your 30-sheet design.
 

Attachments

  • ASSET CONTROL TEMPLATE LM.xlsx
    70.6 KB · Views: 2
Hi Luke

Thank you so much for your input! Did not think I'll get a quick response like I did, so thank you..

The amended sheet looks really good as it ultimately does what I need it to do. I love how you used the PivotTable on the In/Out sheet. To be really honest with you, I have no clue as to how the formula's are used, but this is challenging for me as I love to learn new things. I only recently started working with SUMIF formulas,, pretty much all new to me.

This sheet will ultimately be used to capture everyday's movements by other staff not so much clued up with XL. It needs to be simple to use and that is the main goal I'm going for. I will be testing it for a couple of weeks to make sure it works well.

PS.. Thank you for the link,, Will definitely have a read!

Lee
 
Hi Lee,

As you are getting ready to design the system from "scratch", so to speak, let's make sure we set things up the best way possible. WHen working in Excel, it's best to keep all your data in one central location (sheet), and then use the other sheet(s) to perform analsys on that data. If you start spreading data over multiple sheets, things get complicated, and XL doesn't work well. In the attached, I've modifed the design of your workbook. All the data now goes into a single sheet, and the sheet has an XL table. By having the Date and the Sent/Received as a column in our data, we can easily query the information you need. By using an XL table, our formulas will automatically expand and adjust as new data is added.

You can see the new formulas in your Summary sheet. for your In/Out report, rather than having to build formulas looking all over the place to figure out what the totals were, you can let XL do the work via a PivotTable. I've tried to match your previous layout.

Take a moment to review some of the "best practice" ideas here:
http://www.ozgrid.com/Excel/ExcelSpreadsheetDesign.htm

If you absolutely hate my ideas, that's fine, and we can try to come up with a solution for your 30-sheet design.

Hi Luke

Just a quick question...

Once I have filled in all the details on the data sheet, these details all appear on the In Vs Out sheet. However, I would like to delete the old data (examples you made) to start affresh. When I do, the old data still remains on the last sheet. How would I be able to clear this?

Lee
 
Hi Lee,

As you've discovered, sometimes PT's like to hold onto old data. Thankfully, there's a way to force them to release the old data. with your workbook open, run this macro, and it will "reset" the pivot tables.
Code:
Sub DeleteMissingItems2002All()
'Ctrl+Alt+q

'prevents unused items in non-OLAP PivotTables
'pivot table tutorial by contextures.com
Dim pt As PivotTable
Dim ws As Worksheet
Dim pc As PivotCache

'change the settings
For Each ws In ActiveWorkbook.Worksheets
  For Each pt In ws.PivotTables
    pt.PivotCache.MissingItemsLimit = xlMissingItemsNone
  Next pt
Next ws

'refresh all the pivot caches
For Each pc In ActiveWorkbook.PivotCaches
  On Error Resume Next
  pc.Refresh
Next pc

End Sub

To install a macro, right-click on any sheet tab, view code. This will open the Visual Basic Editor. Go to Insert - Module. Paste the code into the space that appears. Then hit the play symbol at the top. You can then close the Visual Basic Editor.
upload_2015-5-28_9-31-15.png

Glad you like the preliminary concept. As you may have already learned, the SUMIFS function is the next step from the basic SUMIF function. It lets you give multiple criteria to the SUM. We want to sum all the rows from Rolltainers/Pallets/Etc. column where the Store matches col B, and if it was a Sent or Received. The INDEX function is helping the function know which column of the table to look in for the sum. Aka, where is the pallets column? Where is the Lugs column?

As always, feel free to ask if you have any questions. Love to help someone eager to learn. :cool:
 
Back
Top