How can I rollup a sum using an id in two separate sheets?
I have one sheet, the Purchasing Request sheet, which tracks a single request made. The ID column serves as a primary key here.
On the other sheet, I have items that are tracked and related to a given request. The ID column in this sheet serves as a foreign key, which ties back to the one request made.
I would like to try and build a formula in the Purchasing Request sheet that basically rolls up the sum for that row. So, in the images above, the [Request Total] column for row 1 should equal 659.38. Because the formula (in theory) would be able to go to the Purchasing Request Items sheet, match the IDs, and sum the totals that are only related to the ID. I'm not too sure how to go about setting this formula up or if it's even possible.
Note, I do not want to roll up to a report or some type of pivot because I want to build some automation into the Purchasing Request sheet.
Best Answer
-
@Kimberly I figured it out. Thank you. If you respond to me, I will accept your answer as a solution. I can't see the option anymore under your original comment.
Answers
-
You should be able to use the SUMIF function with a cross sheet reference.
=SUMIF({Request ID},[ID]@row,{Total})
-
@Kimberly, where does the cross sheet reference go, though? I'm trying to integrate it with no luck.
-
@Kimberly I figured it out. Thank you. If you respond to me, I will accept your answer as a solution. I can't see the option anymore under your original comment.
