Hi,
I'm trying to make a day organiser for my school, detailing which staff members need to be where and at what time. I have made a drop down menu for staff selection and used the highlight tool to highlight any duplicates. I need to find a way of removing staff members from the list once they have been allocated and place them back on the list if they have been deselected. Can anyone help?
Cheers
Rig
If this "list" you're referring to is on a Range, Do this:
Assuming "U3:U12" is where you will "use" the names.
Please Login or Register to view this content.
But keep in mind it is very difficult to guess what you want without an example workbook.
John.
"I excel at jumping to conclusions"
Hi,
Thanks for responding. I have included an example of the day sheet as it is. The lists are to the right. You will see that i have drop down boxes populating all areas where changes can occur.
Cheers
Rig
hmm, It's very doable, but still, you have multiple cells (C12:E20 ; C23:E31 ; H12:H16...) with data validation that use the list to the Right (AC2:AC24)
which EXACT cells you want to remove from the list? All of them? some of them?
And how many can you have per Staff? For example, you have multiple on S3 and S6...
If you can clarify that, i will certainly help you
Hi,
I have made some changes to hopefully make the adding of colour will make it easier to comprehend. THe boxes in columns c12-e12 down to c20-e20 need to use the list ad1-ad27.
THe box b5-q5 down to b8-q8 need to use the purple lists ad44- ak44 depending on the timing (eg B6-8/ C6-8 need to use the list on cells AD44- AD62). I have added in blanks in case our staff list changes.
The lessons in columns S-AA need to use the coloured lesson column (AM), room for that lesson (AE28-35) and staff member for that lesson e.g. AG2- AG27 for lesson 1.
Hopefully having a dedicated list for each set of data will help.
Basically once a member of staff/ room/ subject has been allocated from the list i need it to disappear from the options. If an option is removed from the table i need it to reappear on the list. Does that make sense?
Many, many thanks for your help.
Rig
Hi,
And holy **** that took so long to do
Follow attachment.
Please check every option to be sure I didn't miss anything
Cheers
Words literally fail me! Thank you so much! I just hoped someone could give me advice on how to do it but you have gone a million miles further! How did you do it?
I'll have a play with it to see how it works. Thank you again.
Hi,
I have had a play around with it and it doesn't seem to be working. DO i need to refresh the document after making changes using the drop down menu?
Hi Rig,
No, you don't need to refresh. The workbook I gave you works like this:
On the colored columns you showed to me, I put a formula on each cell. So if the formula "finds" for instance, the Name "MR" on the range I selected, it displays a "" cell. If it doesn't find on the range, it displays the Name "MR".
The drop down Data Validation, is applied on the same range, and the list is used from the same colored column. So when I pick the name "MR", the formula on the colored column sees (finds) the name, and wipes it out from the list.Hope I made that a little bit more clear.
So if you are being able to pick repeated Names, maybe I specified the ranges a little bit different than how you imagined. But since I'm not inside your head, I'm afraid you'll have to go on from here. I'm happy to give more clarification on the formulas/drop downs, just ask me.
Hi,
Thank you for taking the trouble to help me, i'm very inexperienced in excel (you might have noticed!) Do you have any links to the type of formula you have used? Just so i can learn how it works?
Cheers
Rig
Hi it appears the first tip has worked a treat! I'll have a play today and see. Thank you so much!!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks