Data validation dependent

[Arnodekkers1995]

Hi,

I'm making a sheet to easily click together invoices/offers.
Therefor i've got a data-sheet which has all products on it (per row), with also colums for each 'color' of this product, and the prices of these combinations.

Description	Yellow	Blue	Red
Table	$ 10,00	$ 15,00	$ 20,00
Chair	$ 20,00		$ 26,00
Couch		$ 85,00	$ 95,00

Now, in the main 'Offer' sheet, i'd like to be able to select a product (from dropdown), and then select a color of this product, for example it's colour. These two together should bring up a price which then can be displayed in the offer.

Qty	Product	Color	Price	Subtotal
5	Table	Blue	€ 15,00	€ 75,00
3	Chair	Red	€ 26,00	€ 78,00
Total				€ 153,00

This is all not rocket-science and i've got it working well, there's only one thing i'd like to solve:
Right now, there are some products which are not available in a certain configuration (in my example sheet, the chair is not available in Blue, and the couch not in Yellow). Therefor, i'd like the 'color' dropdown to also not show them.
Is there any possible way to get this achieved?

Thanks in advance!

 

[jasonb75]

This is all not rocket-science

It wasn't, but now it is heading in that direction. Data validation rules don't accept arrays, which is what would be needed in order to do what you want with the existing layout. The best that you will be able to do is something like this. Note that the validation rule for the product is deliberately set up so that it can not be changed once a colour has been chosen, the colour must be deleted first. Without this restriction it would still be possible to select some invalid combinations.
Note that the yellow, orange, and green tables are all named as per the product that they are related to.

test file 16.04.21.xlsx
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1								Product		Table		Chair		Couch
2	Qty	Product	Colour	Price	Subtotal			Table		Colour	Price	Colour	Price	Colour	Price
3	5	Table	Blue	15	75			Chair		Yellow	10	Yellow	20	Blue	85
4	3	Chair	Red	26	78			Couch		Blue	15	Red	26	Red	95
5										Red	20
Sheet5

Cell Formulas
Range	Formula
E3:E4	E3	=SUMPRODUCT(($K$3:$O$5=[@Colour])*($K$1:$O$1=[@Product]),$L$3:$P$5)
F3:F4	F3	=[@Qty]*[@Price]

Cells with Data Validation
Cell	Allow	Criteria
C3:C4	List	=IF(D3="",INDIRECT("Product[Product]"),C3)
D3:D4	List	=INDIRECT(C3&"[Colour]")