Data Validation - reversing the validation!

Question

I have a spreadsheet where

Column B = drop down list of 'Contractor', 'Supplier', or 'Both'.

Column C = drop down list of Yes or No where Column B is 'Contractor' or 'Both', but drop down list of just 'unnecessary' where Column B is 'Supplier' (using INDIRECT within Data Validation).

I now need Column G to show a drop down box of 'unnecessary' where Column B shows 'Contractor', but a drop down box of Yes or No where Column B shows 'Supplier' or 'Both'.  ie.  Using the same reference cells as Column C, but reversing the dropdown box options.

I can't work out how I use the same values but reverse the validation.

Can anyone help please?  Many thanks.

Answer 1

I am also using excel 2007 and there shouldn't be any problem. Even I myself downloaded and checked it. 

Try to download it and then open it on your laptop.

Answer 2

File attached

http://sdrv.ms/11cI1F4

I'm running Excel 2007, and your file is telling me it doesn't recognise it.  :-(

Answer 3

File attached

http://sdrv.ms/11cI1F4

Answer 4

First you Give this formula to a new helping cell, say at H1,  ( You may hide this cell later)

=LOOKUP(B1,{"Both","Contractor","Supplier"},{"Both","Supplier","Contractor"})

I am assuming your column B ,  at cell B1, has a drop down list of 'Contractor', 'Supplier', or 'Both'.

Now in Column G at cell G1, in Data Validation, after selecting 'List' give this formula

=INDIRECT(H1)

That seems to be giving me the 'Unnecessary' option for Contractor.  But not the "Yes" or "No" option for Supplier or Both.  Am I missing something?

Answer 5

First you Give this formula to a new helping cell, say at H1,  ( You may hide this cell later)

=LOOKUP(B1,{"Both","Contractor","Supplier"},{"Both","Supplier","Contractor"})

I am assuming your column B ,  at cell B1, has a drop down list of 'Contractor', 'Supplier', or 'Both'.

Now in Column G at cell G1, in Data Validation, after selecting 'List' give this formula

=INDIRECT(H1)