conditional droplist INDIRECT()

[merlin_the_magician]

I need help using droplist and INDRECT()



For construction work tenders, we would like the contractors to use equipment that has least environmental impact. We want to try this means of an incentive premium. Equipment that has less emissions is preferred.

I have built a worksheet that works the way I would like it to, but it is too complicated, really.



In an effort to make things more user-friendly, I have been experimenting with conditional droplists, using INDIRECT().

Users choose an equipment piece, the corresponding enige category and fuel.



This works, sort of… however very soon after starting this little experiment, I ran into 2 major problems.

• Droplists are conditional, meaning one depends on the other. However, this only seems to work top-down, not bottom-up. That means users (accidentally or purposefully) may enter invalid combinations. Can I prevent this from happening? It appears cross-referencing conditional formatting is not possible.
• Depending on the value chosen from the droplist, the next droplist should have limited choise. Practical thing in the EXAMPLE WORKSHEET I added: an electic vehicle should not use fuel.

Can anybody me with this please?



Oh…. prefer NOT to use macro’s. This will likely violate network-security standards.

 

[jasonb75]

I don't think that it will be possible to make it work both ways without vba (macros), you could try this as the list formula for B5, it will only allow B5 to be changed if B6 and B7 are both empty.

=OFFSET(equipment,0,0,IF(AND($B$6="",$B$7=""),ROWS(equipment),0),1)

Similarly, for B6,

=OFFSET(INDIRECT(B5),0,0,IF($B$7="",ROWS(INDIRECT(B5)),0),1)

There might be a simpler way of doing it, but validation is very fussy with the formulas that it will accept.

 

[Toadstool]

Hi Merlin_the_Magician,

Problem 1: The Data Validation Lists are data entry so won't change after you've made a selection. e.g. if you select small boat in B5 but then delete small boat from the equipment list in F10 it will still show small boat in B5.

Problem 2: Your Fuel Data List is fixed. To make it restricted to previous selections you'd need to build a separate fuel list from fuels which are valid for the contents of B5 and B6.

 

[Peter_SSs]

It appears cross-referencing conditional formatting is not possible.

Would this Conditional Formatting help?

Here I had Tractor in B5 but changed it to small boat & the cells are highlighted indicating an error

merlin_exampledroplist.xlsx
	A	B
4
5	Equipment	small boat
6	Engine category	Stage 4 / Tier 4B (final)
7	fuel	Regular
8
Blad1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	Expression	=AND(B7<>"",B6="Electric")	text	NO
B5:B6	Expression	=AND(COUNTA(B$5:B$6)=2,ISNA(MATCH(B$6,INDIRECT(SUBSTITUTE(B$5," ","_")),0)))	text	NO

And if Electric is in B6 and anything in B7 it gets highlighted.

merlin_exampledroplist.xlsx
	A	B
4
5	Equipment
6	Engine category	Electric
7	fuel	Regular
8
Blad1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B7	Expression	=AND(B7<>"",B6="Electric")	text	NO
B5:B6	Expression	=AND(COUNTA(B$5:B$6)=2,ISNA(MATCH(B$6,INDIRECT(SUBSTITUTE(B$5," ","_")),0)))	text	NO

 

[jasonb75]

When posting my earlier reply, I hadn't noticed that 'Fuel' was not in a dependent list, to make that part work properly it would be best to create a second table for fuel types based on the engine in the same format as the engine category table, i.e.

Fuel category
geen	Level 2	Electric	Hydrogen
Bio-based diesel	Regular	Electric	Hydrogen
	GTL
	HVO