I have cells in a column that has data validation and point to a list with two values "HERE" and "GONE". I want this list to only appear for the user if the criteria in two other cells equal certain values. I tried this =IF(AND(C2="IN",D2="OUT"),STATUS,"") in the
cell's data validation under the formula for LIST and it will not work and pops up a message box saying that the LIST SOURCE MUST BE A DELIMITED LIST, OR A REFERENCE TO A SINGLE ROW OR COLUMN.
The data validation exists for row 2 to row 500. So if C2 & D2 have "IN" and "OUT" then E2 will have data validation with a drop down list with two values "HERE" "GONE". If either C2 or D2 do not have the values mentioned, then there will be no drop down list and this logic extends to row 500.
I have also tried the following:
1. formula in data validation. STATUS refers to
a RANGE with the two values.
=IF(AND(C2="IN",D2="OUT"),STATUS,"")
2. formula in the Status cells
=IF(AND(C2="IN",D2="OUT"),"Here","")
=IF(AND(C2="IN",D2="OUT"),"Gone","")
and use the range named Status in the validation.
I have been told to put code in the worksheet change event and use OFFSET to get the values for the correct row in cols C and D and copy them to another part of the worksheet and use the indirect command to get my list values. I tried that, but it did not work for me. I need the drop down list values in col K to disappear if the value changes in C and D for that row and even if a value is chosen from the list before that value should auto erase if col c or d for that row changes.
Does this make sense?
Maybe more detail is needed. Please advice.
Thanks
Michael
cell's data validation under the formula for LIST and it will not work and pops up a message box saying that the LIST SOURCE MUST BE A DELIMITED LIST, OR A REFERENCE TO A SINGLE ROW OR COLUMN.
The data validation exists for row 2 to row 500. So if C2 & D2 have "IN" and "OUT" then E2 will have data validation with a drop down list with two values "HERE" "GONE". If either C2 or D2 do not have the values mentioned, then there will be no drop down list and this logic extends to row 500.
I have also tried the following:
1. formula in data validation. STATUS refers to
a RANGE with the two values.
=IF(AND(C2="IN",D2="OUT"),STATUS,"")
2. formula in the Status cells
=IF(AND(C2="IN",D2="OUT"),"Here","")
=IF(AND(C2="IN",D2="OUT"),"Gone","")
and use the range named Status in the validation.
I have been told to put code in the worksheet change event and use OFFSET to get the values for the correct row in cols C and D and copy them to another part of the worksheet and use the indirect command to get my list values. I tried that, but it did not work for me. I need the drop down list values in col K to disappear if the value changes in C and D for that row and even if a value is chosen from the list before that value should auto erase if col c or d for that row changes.
Does this make sense?
Maybe more detail is needed. Please advice.
Thanks
Michael
