Implement something akin to cell validation

[Ideafix]

Hello there,

I’m trying to implement something similar to cell validation, where a value that doesn’t meet the criteria gets rejected and prompts an error message, but I’m not sure if it’s possible to do it the way I want.

I created this example to show what I want to do. It’s not the actual spreadsheet I’m working on and these two tables would be in different sheets - one for acquisitions and available stock and another for items consumed.

What I want to do is to have cell validation on column C, so that it returns an error if the available stock for that particular item is zero (by checking column H).

In this example, I want that cell validation rejects any value input into cell C7 because there are no more peaches in stock, while presenting an error message like “Stock Depleted”.

This validation should apply to the entire column C.

Is this something that can be done?

Thanks in advance for any insights into this.

[keme1]

Most likely it can be done.

Suggestion for custom validation formula for C2:

C2<=(C2+VLOOKUP(A2;$E$2:$H$6;4;0))

Validation can be copied by “clone format” paintbrush tool.

---

If the suggested validation formula doesn’t work:
If you attach the file you have (or a sample with only the relevant part, as pictured) you relieve the helpers from creating the dataset from scratch, and we also see exactly what you have already done. Miminal effort from you yields less extra work for us, and better background for providing assistance.

[Villeroy]

LibreOffice comes with a database component.
SimpleInventory_embedded.odb (54.8 KB)

[Ideafix]

@keme1
Thank you.
That didn’t work for me, but a variation of this did. I ended up using

VLOOKUP(A2;$E$2:$H$6;4;0)>=0

This works as intended

[Ideafix]

@Villeroy

@Villeroy

LibreOffice comes with a database component.
SimpleInventory_embedded.odb (54.8 KB)

Thank you.
I do use Base for a series of things, but in this case this has to be a simple tally sheet that a person can use on an Android tablet. Google Sheets and Excel for Android can handle these ODS files fairly well, while ODB is troublesome to use.