Formula/Rollup Pulling from 2 Tables into 3rd Table


#1

I’m going to try to make sense of a wall I’ve been hitting for three days…I appreciate any help!

I’m working on creating a corporate budget to help a co-worker get organized.
Table 1 - Budget Categories (How much has been allocated for each category, how much has been spent, links to what receipts were spent in the category and who has spent in each category)
Table 2 - Receipts (What was spent where, links to staff that spent, and links to what category it is coming from)
Table 3 - Staff (SHOULD BE: How much of the category’s total (ALLOCATED AMOUNT field from Table 1) each person (NAMES field from Table 3) has spent (AMOUNTS field from Table 3) in percentage form displayed on Table 2)

Currently, every table links to every table. I’ve tried formulas (sums and if’s mostly), rollups, and lookups, and I haven’t found anything that works. This doesn’t seem like such an out of the box request that it wouldn’t be possible.

I’m thinking either I’m totally hitting every formula except the right one, OR I need to change some fields around for some reason.

Right now, this is totally a mock budget until I figure it out, so I’m open to a fresh start or fixing what I already have. Any ideas?

I should also probably mention that I’m on a free account for now - so no access to blocks.


#2

Can you post a link to the base? (I’m assuming, if it’s a mock budget, you won’t need to sanitize it too much; if that’s incorrect, feel free to send me the link by PM.)

Post a read-only link to the base, with copying permitted; that way, anyone who wishes to help can dupe a copy of the base and fiddle around to their heart’s content without risk of muddying your data.

Thanks!


#3

Absolutely!


#4

Let me know if that doesn’t work!


#5

Thanks! I’ll try to take a look at this shortly.


#6

Any thoughts on this?


#7

My apologies — I bookmarked this but somehow lost it while syncing browsers. I’m taking a look at it now — assuming you’re still looking for assistance…

Edit: Ah, I think I see where you’re going with this. It’s going to take a little time to block this out; I’ll try to get to it later today or tomorrow…


#8

I was able to get what I needed accomplished. Thank you, though!


#9

Hi guys! I’m looking for some help :pray:

I don’t know if @Sarah_Comer’s question was same as mine, but the title of the topic seems similar.

I have a PROJECTS table that should rollup, for each Project, the values of the products in an ORDER LIST table. This table holds the QUANTITY of each item to be ordered, and some of these quantities are dependent on some data contained on a separate CIRCUITS table. This is the main table, where all the technical decisions are typed in.

My problem is how to calculate correctly the dependent QUANTITY field.

As advised by @W_Vann_Hall, here is shared a mockup of the part of my base that I’m looking for help:
Circuits Projects

The instructions on how the QUANTITY field should work is detailed on the shared base, but here is the same information that is there:

  • For items that are classified as “Contactor”, I want the Quantity to be the number of times the item is selected in “Circuits” table for each Project.
  • For items classified as “TC”, the Quantity should show the sum of “Phases” field it is related in “Circuits” table for each Project
  • For the “Fuse” class, the Quantity should be the sum of all “Phases” values for the Project.
  • Finally, the Quantity for “CPU” class items should be typed individually in this “Order list” table, not linked to any value on Circuits table.

Sorry for any English mistakes, not my native language (Brazilian Portuguese)