How to have a cell be a limit for another sheet

Mitchell_Daly · 03-14-2019 06:38 AM

I am trying to have my app let me know when I am using too much of a product. I figured if I could set a limit for each type of product on another sheet and have it reference the original sheets column I could get that to work. I am also looking at data validation trying to come up with a formula that would work there, but to no avail. I am just not sure how to make the app work around this. Any ideas?

Aleksi

IF you have a product table and you have created a related table structure, you could read the limit value with the deref expression like [ProductID].[Limit]. Check this article about related tables.

Mitchell_Daly

I am still looking through the References Between Tables, but I am thinking I am overcomplicating this somehow. I have a sheet keeping track of what product I am sending to each site and I have another sheet indicating the value of a unit and I would like to add the limit of how much of the product I have remaining. I would like the app to notice if I am trying to add potatoes to an order that exceeds the amount of potatoes I have to send. I am not sure if that makes sense…

Mitchell_Daly

Reza2

As Aleksi mentioned, you could use reference between tables, so in your case if you want the Amount column in Shopping List table not to exceed the Units Needed in Breakdown table, you could have a Ref column type (e.g. “RefToBreakdown”) in Shopping List table that refers to Breakdown table, then within Shopping List table you can access it like this and use it in any expressions to evaluate against current amount in invoice:
[RefToBreakdown].[Units Needed]

Mitchell_Daly

That helps in a different issue, but I am not trying to have the app auto-fill a units needed. I am looking to have a quantity diminish and stop me from taking more from it when it reaches 0.

Reza2

Well, since the amount limit is coming from another table, the method would be similar; you do not have to necessarily use it to auto-fill a units needed; I referred to that column as an example; you might need to have for example a Virtual Column that calculates the total of shipped amounts, and remaining amount then using that same De-reference method access that value and if the entered value is more than the remaining value consider it invalid using Valid_if, so user cannot save the record unless it is equal or less than remaining amount.

Mitchell_Daly

I tried a valid if formula, but it is just saying everything I enter is invalid even if the number is under the amount remaining. I am also unsure how to make the number of cases specific to the type of produce as well. So for instance. I want to have it tell me I ran out of potatoes specifically and when I enter apples as the product type it knows I have a specific quantity left. Here are a couple of screenshots of where i am.

Mitchell_Daly

Reza2

You may want to read this document too for more details: