Ok, here's something I just can't figure out,...

expressions
(Helen Skiba) #1

Ok, here’s something I just can’t figure out, and I’m not sure if I did it right in the first place. I have a list of plants that I grow, and I keep track of seed inventory with the app. I also make a weekly planting list using the app. Seed inventory and planting list are in two different sheets in my spreadsheet. I created a dropdown list using ENUMLIST, with a

Valid_If constraint,

so I can select which varieties to plant each week. This works fine. However, I’d like to be able to see only the varieties that I have stock of - i.e., I don’t want to be able to choose a variety to plant if I don’t have any seed. The Valid_If constraint says to show me the varieties in my seed inventory that match the plant I want to sow that week. I want another constraint that shows me only the varieties that match what I want to plant, AND that have an inventory value >0. I don’t know if I’m explaining this well, but here is the expression in my Valid_If constraint for ENUMLIST:

=Varieties[Variety] (show me the column “variety” from the sheet Varieties).

I tried = AND(Varieties [Variety], Varieties [Stock]>0), but it said “Cannot compare List with Number in (VARIETIES [Stock] > 0)”

How can I go about this differently?

Thank you!!

(Bellave Jayaram) #2

Try this SELECT (Varieties [Variety], [Stock]>0)

(Helen Skiba) #3

@Bellave_Jayaram Thanks so much! This worked, but it does show me ALL the varieties that have Stock >0, not just those for the plant I’m currently looking for. So I need to find rows in my inventory sheet that have stock >0 AND match the plant type in my planting list sheet. IDK if that makes sense but this is a good start, thank you again!

(Helen Skiba) #4

I figured it out, thanks to @Bellave_Jayaram, just had to get that AND expression in the right place: =SELECT(Varieties[Variety], AND([Stock]>0,[Common Name]=[_THIS].[Planting List]))

Thanks!

(Bellave Jayaram) #5

@Helen_Skiba , glad you figured it out!!