Data Validation Hide Previously Used Items in Dropdown

[Abal1977]

Hi guys, could you please help me in finding my error

[MatrixMan]

You're going to have to be more specific. What is the problem? What is supposed to happen versus what is currently happening? And there is no code in this file (it's an xlsx file). If this is not a VBA problem, I suggest you post it in the normal Excel forum.

[Glenn Kennedy]

Come on.... we are not mind readers!!!

What do you want done & where?

[Abal1977]

Hi, Sorry guys
I will explain ....
I'm a Imaam and I'm also in charge in preparing Weekly SErmons in different Mosques across my country.
Our organisation is comprised of 20 Imaams and our services is required in about 30 Mosques.
Each Mosque has it's own requirement, for example, some needs us to deliver the sermon each and every week, some three times monthly, others twice or even only once.
In Sheet "Khutbah" is the name of Mosques (Masjids) and their requirements.
In Sheet "Khatweeb" is the name of Imaams.
Sheet "Individual" i just worked on it as "ratcat" suggested one memeber with it: http://www.contextures.com/xlDataVal03.html
Normally i do prepare the scheme each three months.
What i want is that as soon as i'm using one Imaam from my dropbox, his name just vanishes from the dropbox, so this will lessen errors.
I hope that it's more explicit now, and you can ask if it is still incomprehensible.
Thanks
Hope to hear from you soon.

[Glenn Kennedy]

Aas Salaam Aleikum, my friend. I will look at this in the morning.

[JeteMc]

The proposed solution isn't as elegant as the one from 'contextures'. It includes a number of helper columns on the far right side (XER:XFD) of the first sheet. It may not be using the two tables on the third sheet as they were originally intended either. The left table on the third sheet is populated by the formula:

Please Login or Register  to view this content.

The right table on the same sheet in populates by the array entered formula*:

Please Login or Register  to view this content.

The helper columns on the first sheet reference the names in the right table (third sheet). The formula used in the data validation is:

Please Login or Register  to view this content.

This formula was copied across row 2 as all of the cells in that row are blue. The formula for each column was then copied only to the blue cells in that column, so as to preserve the pink cells which apparently do not need assignment.
Note: some of the blue cells have names simply for the purpose of demonstrating how the data validation list hides the names of persons that have already been assigned in that column.
*Array entered formulas are activated by simultaneously pressing the Ctrl, Shift and Enter keys while the cell is in edit mode. This needs to be done before copying the formula to other cells.
Let us know if you have any questions.

[Glenn Kennedy]

I'm still not sure where you want the DD list to be and how it is to be populated. However, take a look at this sheet and see if it is doing what you need...

[Abal1977]

Hi JeteMc,
Thank you very much for the help
Yeah at first glance and working on it for some cells it's working
Hope that there won't be any problem
Thanking you once more.

[Abal1977]

Thank You Glenn Kennedy
It is what i wanted
But as Jete Mc has worked upon my own schedule, it is working well.
Thanking you also for your contribution.

[JeteMc]

You're Welcome and thank you for the feedback. Please take a moment to mark the thread as 'Solved' using the thread tools link above your first post. I hope that you have a blessed day.

[Abal1977]

Hi, Sorry guys
I will explain ....
I'm a Imaam and I'm also in charge in preparing Weekly SErmons in different Mosques across my country.
Our organisation is comprised of 20 Imaams and our services is required in about 30 Mosques.
Each Mosque has it's own requirement, for example, some needs us to deliver the sermon each and every week, some three times monthly, others twice or even only once.
In Sheet "Khutbah" is the name of Mosques (Masjids) and their requirements.
In Sheet "Khatweeb" is the name of Imaams.
Sheet "Individual" i just worked on it as "ratcat" suggested one memeber with it: http://www.contextures.com/xlDataVal03.html
Normally i do prepare the scheme each three months.
What i want is that as soon as i'm using one Imaam from my dropbox, his name just vanishes from the dropbox, so this will lessen errors.
I hope that it's more explicit now, and you can ask if it is still incomprehensible.
Thanks
Hope to hear from you soon.

Problem solved and thanks everybody for your help.