help with data validation please

[fry]

Hi All

I have a bit of an involved problem...

To try and explain this clearly imagine a number of columns, each of which represents a job, and at the top of each column is a cell that contains a drop down list with people listed in it......so far so good and easy to set up but here's the difficult bit......assuming there were 10 names in the list and I pick one of the names in the first column for the first job.

What I want to happen then is for the name that I picked to disappear from the list in all the other jobs so it can't get picked again. So now the list only shows 9 names. Similarly when I pick a name from the 9 names for job 2, that name now disappears from the list and there are 8 names that show for job 3, etc.

Also, just to make it a little harder, instead of going from job 1 to job 2 to job 3 etc, I might go to job 10, then job 2, then job 6 for example and as I pick the names they disappear from the list whichever way round I do it

I'm TOTALLY confused as to where to start or even if this is possible in excel. It does sound more like a database thing where you could have 2 flags, one for 'available on list' and one for 'used already'

Absolutely any help would be gratefully received

 

[fry]

This is a bit worrying, you guys are usually so quick the answer is here before I finish typing!!

I did think it might be too complicated for poor old excel to cope with but I hope someone proves me wrong

 

[fry]

Anyone???

 

[JimmyG]

Hi Fry!

You might like to check out the data validation examples on the Contextures website...

http://www.contextures.com/excelfiles.html#DataVal

e.g.: DV0047 - Hide Previously Used Items in Dependent Lists ??


Regards, JimmyG

 

[fry]

Thanks Jimmy, I'll take a look!

 

[fry]

That was just what I was looking for Jimmy

A thousand thanks for your help!!