Data validation List if "x" is true, and allows values from 0 to 100 if "x" is false ?

[CROfin79]

First time caller, long time listener..... I have a well established data validation list function that works great. I am having zero luck trying to add an if statement or other solution that will allow the user to enter values from say 0 to 100 when the if statement is false.. Any help is very appreciated. My Data Validation list funtion is =INDIRECT(SUBSTITUTE($L213," ","_")). When i add an if criteria, i don't know how to allow user to enter a value (????). For example =IF(M3="x",INDIRECT(SUBSTITUTE($L213," ","_")),?????)

 

[awoohaw]

Would you be kind enough to help the forum help you? Please post a mini worksheet using the xl2bb add in (link below).
When posting, it may be a good idea to post a mini sheet where the validation is working, and then another where it is not.

 

[CROfin79]

Thanks. For Vegatables I can enter % of bushel for Jan-23. However, for Fruits and Bulk, i cannot enter a number >0.

First time using XL2bb, hopefully the below works as intended.

Fruits and Vegis.xlsx
	A	B	C	D	E	F	G	H	I	J	K
2	Product Type	Charge Type	Product	Jan-23		Product Type	Charge Type		Fruits	Vegatables	Bulk
3	Vegatables	Bushel Pct	Beans			Fruits	Each		Apples	Beans	Rice
4	Vegatables	Bushel Pct	Beans	0.25		Vegatables	Bushel Pct		Oranges	Carrots	Potatoes
5	Bulk	Per Pound	Rice			Bulk	Per Pound
6	Vegatables	Bushel Pct	Carrots	0.50
7	Fruits	Each	Oranges
8
9
10
11						Beans	0	0.25	0.5	0.75	1
12						Carrots	0	0.5	1
Example

Cell Formulas
Range	Formula
B3:B7	B3	=VLOOKUP(A3,$F$3:$G$5,2,FALSE)

Cells with Data Validation
Cell	Allow	Criteria
A3:A8	List	=$F$3:$F$5
C3:C7	List	=INDIRECT(SUBSTITUTE($A3," ","_"))
D3	Custom	=IF($B3="Bushel Pct",INDIRECT(SUBSTITUTE($C3," ","_")),D3>=0)
D4:D6	List	=IF($B4="Bushel Pct",INDIRECT(SUBSTITUTE($C4," ","_")))
D7	Custom	=IF($B3="Bushel Pct",INDIRECT(SUBSTITUTE($C3," ","_")))