Formula to referance availible remaining stock based off used stock
![Caleb W](https://us.v-cdn.net/6031209/uploads/defaultavatar/nWRMFRX6I99I6.jpg)
I am trying to build a sheet to track a number a different brace types. How would I build the formula to take the total owned braces by type, subtract total used by type across several rows.
This would be the place holder for reference of type and total available owned.
This would be the entry as braces are assigned out to a project.
So I would want the Quantity column to minus from the Quantity Owned column by type leaving just what are not assigned out.
Answers
-
I notice there is a Date Return column. Is it possible there will be a date in this column for some rows, indicating that the braces have been returned?
-
@Carson Penticuff yes that is the plan. Once the return date is set then it would be entered into that column. Then once that date is reached, the days on site would stop counting and the braces would then revert back into the available braces.
-
Here is what I envision, please correct me if I misunderstand.
=IF(AND([Brace]@row <> "", [Quantity Owned]@row <> ""), [Quantity Owned]@row - SUMIFS({Quantity}, {Brace Type}, [Brace]@row, {Date Return}, ""))
I have included only the relevant columns in the sample sheets I show above. This would require that you create the following cross-sheet references in the first sheet, pointing at columns in the second sheet. {Quantity} - {Brace Type} - {Date Return}
-
Can this be all in one sheet?
-
To be clear, do you mean you would like ALL of the information in one sheet? The two screenshots I posted are representative of the two that you posted. My second sheet just doesn't contain all of the extra columns, Cost, Days on Site, etc. If you DO want everything on one sheet, that can also be done:
=IF(AND([Brace]@row <> "", [Quantity Owned]@row <> ""), [Quantity Owned]@row - SUMIFS([Quantity]:[Quantity], [Brace Type]:[Brace Type], [Brace]@row, [Date Return]:[Date Return], ""))
Once again, unnecessary columns are omitted.
-
Thank you @Carson Penticuff, that worked out perfect!!
One last questions for you. I am using =NETDAYS([Date Out]@row, TODAY()) to track days onsite since "date out". How can I close out and stop counting days once the date in "date return" column is reached?
-
This will work the same as your current formula if [Date Return] is blank. If [Date Return] is populated, it will give you the number of days between [Date Out] and [Date Return].
=IF(ISDATE([Date Out]@row), IF(ISDATE([Date Return]@row), NETDAYS([Date Out]@row, [Date Return]@row), NETDAYS([Date Out]@row, TODAY())), "")
-
Thank you for the quick response. That is a big help.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.1K Get Help
- 348 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 133 Brandfolder
- 127 Just for fun
- 127 Community Job Board
- 455 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 282 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!