Optional dropdown

First question, I’m hoping someone can help. Please be gentle, I have next to no knowledge of code, and have managed thus far by cobbling together bits and pieces of what google presents.
I have 2 tables, Full Database and Inventory. Both are worksheets in the same google sheet.
Full Database has several columns, relevant are Master UPC, Master Item #, and Master Title. Nothing is set as references.
Inventory is set up, and currently working, with the following:
Column UPC is scannable, and has FullDatabase[Master UPC] as a Valid if constraint.
Column Item # has LOOKUP([UPC], Full Database Master, Master UPC, Master Pop #) in the initial value field. This autopopulates based on the UPC, but still allows me to override with a different number.
Column Title has LOOKUP([UPC], Full Database, Master UPC, Master Title) in the initial value field, and sort(UNIQUE(Full Database Master[Master Title])) in the suggested values field. This autopopulates based on the UPC, but allows me to manually select as well from a pulldown list.
So far so good, a UPC is scanned, and generally it fills in the other fields by itself. In the rare event that a UPC doesn’t match, or is a new one not currently in the database, it allows me to manually input my values. Great.

The trouble comes with the fact that UPC’s in the list are not completely unique. Some of them can have more than 1 item attached to that UPC.
Is there a way that when the UPC has only 1 match, it performs as I already have it and autopopulates, but if there is more than 1 match, it will leave the field empty and instead populate the dropdown with only those items that match?
e.g. single UPC match, number and title are autopopulated, if I hit the dropdown to manually change it I get a list of literally all possible titles to choose from (this is how it currently is), multiple UPC match, number and title stay blank, I hit dropdown and see all titles that match that UPC?

0 1 187
1 REPLY 1

Steve
Participant V

Try as the Initial value expression for the number column:

IFS(
  (COUNT(FILTER("Full Database Master", ([_THISROW].[UPC] = [Master UPC]))) = 1),
  LOOKUP([_THISROW].[UPC], "Full Master Database", "Master UPC", "Master Pop #")
)

Title column would be similar.

See also:



Top Labels in this Space