Correlating data from 2 fields on a linked sheet for a rollup

[Alyse_Middleton]

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

[AlliAlosa]

Hi there!

Not sure if there is a more elegant way to do this, but you could use a conditional rollup for each department. On your [Expenses] table, add one field for each department with a formula like…

IF({Department} = "Admin", {Qty}, 0)

Then, create rollup fields on your [Items] table that point at the new fields you just created on the [Expenses] table (one for each department, like in your screenshot above), with the formula…

SUM(values)

Again, it’s not super elegant - but it should get the job done :slightly_smiling_face:

[Alyse_Middleton]

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.

[AlliAlosa]

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.

Airtable

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:

[Alyse_Middleton]

OKay I think I see what you mean, I think the sheer number of Items and departments might make that less do-able in this case.

But I REALLY appreciate you taking time to show me how to get it done.

[Kamille_Parks]

If you instead record the department as a Single Select or a Link to Another Record type field, you could use the Pivot Table block as opposed to having a thousand columns