Excel drop down list created from col A: number and col B: item, then reducing options

Question

I would like to list all the fruits in my basket, then give fruits to my friends, and doing so options in the drop-down list are reducing automatically.

Column A: number of fruits Column B: fruits Column C: names Column D: dropdown list

For instance, I have 1 ananas, 0 apple and 2 oranges. When I give a fruit to one my first friend, options from the drop-down list are "ananas"; "orange"; "orange".

If I give an ananas to one of my friend, then the drop-down list becomes "orange", "orange" for others, and so on.

Is it possible to do it with Excel? If so, is it possible without VBA, with data validation and regular formulas?

Answer 1

It is possible but involves a few steps.

I assume the fruit amounts are in A1:A5, fruit names B1:B5, friend names C1:C5 and the basket list drop-down selection is in D1:D5. I will use F1:F5 for the list calculations, and G1:G15 will hold the basket list for the drop-down.

1. Calculate on which row each fruit should begin in the basket list, according to the amount and basket usage:
   • F1 =A1-COUNTIF($D$1:$D$5,B1).
   • F2 =A2+F1-COUNTIF($D$1:$D$5,B2).
   • Copy F2 to F3:F5.
2. Create the basket list for the drop-down:
   • G1:G15 =INDIRECT("B"&COUNTIF($F$1:$F$5,"<"&ROW())+1).
3. Create a Data Validation list for D1:D5:
   • In the list source put =OFFSET($G$1:$G$15,0,0,COUNTIF($G$1:$G$15,"<>0")). I assume cells under row 5 in the fruit names will be empty, so remainder rows in the list will get the value 0, hence the "<>0" in the list count.

Now when you select a fruit from the validation list, the list will update accordingly.