I have created a drop down list . for example PTO1, PTO2 , PTO3 .. however, when someone selects PTO1, in same row, other people should not be able to select PTO1. it should start from PTO2 ... how to do it..
Please help me
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Drop Down query
0
Discussion
It seems like you need some type of dynamic drop down list. Can you describe in more detail what you are trying to achieve. Please include a sample file that shows what you are trying to do.
WillieD24 (rep: 547) Jun 30, '22 at 11:40 am
If one person select PTO1
Next person should not be able to select PTO1 instead he should select PTO2
Same, third person should select PTO3 . Not PTO1 or 2
How to add my excel, no option. please help
Next person should not be able to select PTO1 instead he should select PTO2
Same, third person should select PTO3 . Not PTO1 or 2
How to add my excel, no option. please help
prathikpp143 (rep: 2) Jun 30, '22 at 1:26 pm
You should attach your file to this post and delete your second post to avoid the confusion.
I downloaded your file from your second post. (I also fixed your data validation)
Before working on a possible solution I/we need to know:
1) How many PTOx options will there be? Just 5 like in the file, or more?
2) Are people required to make selections in order - PTO1, PTO2, PTO3, etc, or can they pick randomly - PTO2, PTO5, PTO1, etc.?
I downloaded your file from your second post. (I also fixed your data validation)
Before working on a possible solution I/we need to know:
1) How many PTOx options will there be? Just 5 like in the file, or more?
2) Are people required to make selections in order - PTO1, PTO2, PTO3, etc, or can they pick randomly - PTO2, PTO5, PTO1, etc.?
WillieD24 (rep: 547) Jun 30, '22 at 5:30 pm
Prathik.
I see you have now attached a version of the file I provided (rather than your original) to your question. That's not quite right.
You're asking a new question in the Discussion to my Answer- it ought to be a new question saying you have a year of data or whatever Also please remember to delete the second "urgent" question. Thanks
I see you have now attached a version of the file I provided (rather than your original) to your question. That's not quite right.
You're asking a new question in the Discussion to my Answer- it ought to be a new question saying you have a year of data or whatever Also please remember to delete the second "urgent" question. Thanks
John_Ru (rep: 6142) Jul 2, '22 at 1:14 pm
Answers
0
Selected Answer
Prathik
The problem here is that using data validation can lead to circular references (which prevent it working).Here's a solution which avoids that by using a helper column, based on the method shown an article elsewhere: How To Hide Previously Used Items In Dropdown List?
In the attached file, I've used that technique but put the helper columns in hidden columns I:K. The data validation in column B is based on that in K (and the list will reduce as the options are selected).
Currently it offers choices PTO1 to PTO10 but I leave it to you to make reduced options if needed.
Hope this helps.
Discussion
Thanks for selecting my answer, Prathik.
Sorry but I don't have time to extend the example to cover a year - I'm an unpaid volunteer (whereas I guess you're being paid to do this).
You could try copying the sheet several times then cutting the PT column and pasting into one sheet (then hide the sheets with helper columns)
Sorry but I don't have time to extend the example to cover a year - I'm an unpaid volunteer (whereas I guess you're being paid to do this).
You could try copying the sheet several times then cutting the PT column and pasting into one sheet (then hide the sheets with helper columns)
John_Ru (rep: 6142) Jul 2, '22 at 12:48 pm
Hi John,
Sorry to hear that, But even i m not paid for this, Since its for my collect students to apply their PTO. I m trying this to impress my teacher :-(
I could not get this method """""try copying the sheet several times then cutting the PT column and pasting into one sheet (then hide the sheets with helper columns)"""
Please help me sir :-( I tried all posible ways whole night, But still its not happening. please help me
Sorry to hear that, But even i m not paid for this, Since its for my collect students to apply their PTO. I m trying this to impress my teacher :-(
I could not get this method """""try copying the sheet several times then cutting the PT column and pasting into one sheet (then hide the sheets with helper columns)"""
Please help me sir :-( I tried all posible ways whole night, But still its not happening. please help me
prathikpp143 (rep: 2) Jul 2, '22 at 8:27 pm
Thank you so much John... It worked
prathikpp143 (rep: 2) Jul 2, '22 at 9:40 pm
I need a small formula help....Now this has been working as expected... But If Priya Select PTO1 and Prathik as PTO2 and Sandhya as PTO3 .......... If Priya Delete her PTO1..... automatically PTO2 should become PTO1 and PTO3 should become PTO2...... How to do it sir? Please help
Currectly its running on Hide used iteam formula from dropdown list... please help
Currectly its running on Hide used iteam formula from dropdown list... please help
prathikpp143 (rep: 2) Jul 2, '22 at 9:53 pm
Prathik. Glad you got it working.
Sorry but I don't have time to work out how to reallocate PTOs in one is deleted (and it's not a simple formula, given you have data validation and I don't know what a PTO is!).
Furthermore this is a Q&A forum- users ask a clear question and hopefully someone replies- you should not expect us to develop a mini-project for/with you- just to help you when you get stuck. When that happens, you should ask a new question.
Sorry but I don't have time to work out how to reallocate PTOs in one is deleted (and it's not a simple formula, given you have data validation and I don't know what a PTO is!).
Furthermore this is a Q&A forum- users ask a clear question and hopefully someone replies- you should not expect us to develop a mini-project for/with you- just to help you when you get stuck. When that happens, you should ask a new question.
John_Ru (rep: 6142) Jul 3, '22 at 2:53 am