selecting data from a list

[Glenn]

I'm setting up a worksheet to track/reserve the use of equipment.

I have a list of say 20 items.

Column A contains a list of dates for the year.
Column B should contain a drop down list of the 20 values, except for any
values that have already been used in Column C.
Column C should contain the same drop down list - anything from the list of
items - excluding ones that have already been used in Column B.

Is this possible? Any guidance would be appreciated.

Thanks.

 

[Bernard Liengme]

In H1:H20 we have a list off all 20 pieces of equipment
In I1 we have the formula =IF(COUNTIF(C:C,H1),"",H1), this is copies down to
B20
In H1 we have the formula =IF(COUNTIF(B:B,H1),"",H1), this is copied down to
H20
The Data Validation for column B uses list I1:I20, while that for C uses
H1:H20
Of course, there will be empty cells in the two list

If you want the lists to be on another sheet you will need to name each list

Any help?

 

[Glenn]

This doesn't seem to do what I'm looking for, but I don't think I explained
it properly to begin with.

I'm looking for the cells to have a drop down list of all pieces of
equipment, unless a particular piece is already used in another cell in that
row.

For instance, if the value in H1 is Equipment1, H2 is equipment2, etc.

When you click in cell B1 you get a drop down list of all 20 pieces of
equipment, and select one of those, let's say "Equipment1"

Then, when you click in cell C1 you get a list of all pieces of equipment
that haven't yet been used. In this case, the list would exclude the value
"Equipment 1" in the drop down list.

There are cases when it would be possible that the value in C1 would be
selected before the value in B1, so B1 would also need to make sure that the
value wasn't being used in C1 before displaying it in the drop down list.

Thanks.

 

[Bernard Liengme]

Ah, the twist is "used in another cell in THAT ROW"
Very hard to think of a way to adjust a Data Validation list based on a row.
We would need a separate list for every cell.
How about using conditional formatting to make the text red if the same item
occurs more than once in a row?
best wishes
Bernard