Return a value based on the date and section

vstr88889

Hi,

I'm trying to create a sheet for a department production report, This department has 10 sections, each section do different type job but the project is same, i'm keeping all section work in a mastersheet so that i can filter however i want to generate a report.

the Problem i'm facing is the closing stock should be automatically comes to the opening balance based on the section.

1. if the section is filing and the closing balance of the filing should be comes as opening balance on the next day for filing section.
2. if the section is grinding and the closing balance of the Grinding should be comes as opening balance on the next day for filing section. so far i've tried sumifs, collect but failed.

any suggestions would be appreciated.

Brian_Richardson

Just a side note before the solution, you should really name your columns and not leave them as Column3, Column4 etc. That's how Excel works, but in Smartsheet columns are really fields in a database and they hold meaning. Plus it will be way less confusing when looking at your formulas.

Although the formula could be all in one, I have it broken out below into two columns so you can see what's happening and verify that it's calculating correctly.

1. Rename Column3 to Date, Column22 to Section, Column29 to Opening Balance, and Columnxx to Closing Stock. Suggest renaming all your columns to match their intended data.
2. Make sure the Date column is a "date" type of column in the properties
3. Add a column called "Last Filing and Grinding" and make it a checkbox column
4. Enter this formula in the Last Filing and Grinding column, then right click the formula cell and choose Convert to Column Formula: =IF(OR(AND(Section@row = "Filing", COUNTIFS(Section:Section, "Filing", Date:Date, >Date@row) = 1), AND(Section@row = "Grinding", COUNTIFS(Section:Section, "Grinding", Date:Date, >Date@row) = 0)), true)
5. Add an column called "Opening Balance Override" and make it a text/number column. You'll use this column to enter numbers manually and override the calculation.
6. Enter this formula in the Opening Balance column. =IF([Opening Balance Override]@row<>"",[Opening Balance Override]@row,if(Section@row = "Filing", SUMIF([Last Filing and Grinding]:[Last Filing and Grinding], true, [Closing Stock]:[Closing Stock])))

Here's how this all works:

• The Last Filing and Grinding column does two checks in an OR statement
  • Checks to see if it's a Filing row, AND if there's any other Filing rows after it's Date. If there's only 1 after it, then it knows it's the last Filing row that needs to contribute to the next one.
  • Checks to see if it's a Grinding row, AND if there's any other Grinding rows after it's Date. If there's no Grinding rows after it, it's the last Grinding row that needs to contribute to the next Filing row.
• If the OR statement is true (meaning, if either of those checks above are true), then the checkbox gets checked.
• The Opening Balance formula first checks to see if you put anything in the Opening Balance Override column, and if so it takes that value and stops. If not then it sums up the Closing Stock column from the rows where "Last Filing and Grinding" is checked.

vstr88889

Hi Brian, First of all thank you for the reply, I'm sorry i'm a newbie for smartsheet and formulas, I've tried exactly as you instructed but i'm getting circulating and blocked error. i've attached a screenshot for your reference.

Brian_Richardson

Closing Stock shouldn't have a formula in it. Because the Opening Balance refers to Closing Stock number to know what to pull in for Opening Balance, if the Closing Stock number is somehow calculating off Opening Balance, that's your circular error.