I am creating an inventory tracking spreadsheet based on specific needs I have for reporting. I need to be able to say that on a monthly basis - X items have been purchased and assigned to A,B,C+ departments for Y dollars. And I need at all times a running list the total stock owned of each item and which department it is signed out to.
Currently I have these tables:
- “Items” spreadsheet with 1 column listing all possible items, and the remaining columns listing each potential department they could be assigned to.
- An Expenses spreadsheet which is linked to the Items spreadsheet that tracks the date purchased, the Department assignment, the Items purchased (linked to the items spreadsheet), price quantity and information about who requested and who approved the purchase.
- Staff listing
- Department listing
I would like to figure out a way that I can say IF item purchased for this row is tracked to X department then add quantity purchased to total items for this cell. Or stated differently if all of the same items assigned to the same department would show as a number in the appropriate cell on the Items sheet. (Or I suppose on a 3rd sheet)
Like example below
I’m not sure I see how that would work -
Here is how expenses are recorded
Or maybe I need to redo the whole thing.
I don’t think you’d need to redo it… From what I can see it looks like it’s set up just fine! However, without seeing how the other tables are set up it’s hard to tell you if what I mentioned would work/even be practical. If you have lots of different options for {Department/Class}, you might want to go in a different direction.
The sample base below shows what I’m talking about - I used three departments as an example.
Items/Departments (Example Base) - Airtable
Explore the "Items/Departments (Example Base)" base on Airtable.
You can also read more about conditional rollups here :slightly_smiling_face:
