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
