👉 Up4Excel Downloads Library: »» ml.up4excel.com/library ✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
Hi John. Nice one! If the user has Microsoft 365, then it can be done in one formula without helper columns. Using your template, enter the following in cell J5: =SORT(FILTER(D5:D21,NOT(COUNTIFS(B5:B21,D5:D21)))). Then for the range B5:B21, define Data Validation as a List and the Source is: =$J$5#. This will produce the same effect that you demonstrated. For every entry inside the range B5:B21, the FILTER function will spill the remaining entries which then become the available choices for the Data Validation list. Using the # character on the spilled range, makes the dropdown dynamic to whatever remains in the list. It is amazing what can be done with the new functions.. a real incentive for anyone using legacy EXCEL to upgrade and make their life easier. I hope someone finds this useful. Thanks for sharing your tips and tricks :)) Thumbs up!!
This is another excellent improvement suggestion Wayne. I've just tried it and it works a treat. Really clever use of COUNTIFS in there I wouldn't have thought of myself too. I'm really looking forward to getting some videos out there on Excel365 now I've been using it a bit, there's a load of stuff dynamic arrays facilitate that would take ages with conventional formulas - your comment is one example of many. Thanks for sharing 👍👍
@@Up4Excel Thanks John! This is another variation that is even cleaner: =SORT(FILTER(D5:D21,COUNTIFS(B5:B21,D5:D21)=0)). All the non-matches will evaluate to 0 and a test of = 0 then becomes TRUE which feeds back into FILTER. Fun to come up with different ways to get the solution :)) Thumbs up!!
📗 Get Your Video Workbook Template Here: »» cutt.ly/up4v2005MFD 🎯 When it's Gone it's Gone!!…..This special dropdown list removes used items as they are choosen, and even puts them back if choices are changed. No one can make a duplicate choice with this automated decreasing dropdown list.
@Up4Excel This is very helpful! Would you have some instruction to get the dropdown list (column B in this video) to work on a completely different tab of the same excel document? Thank you!
Gald to help 👍 I think you can just link the dropdowns to anything, even on another sheet? You might have to adapt the formulas to include the sheet names.
Hi There, this is absolutely amazing. I copied your formulas courtesy of you and optimised our rosters and everyone loves it! Thank you very much!! Just one quick question if I may ask is that somehow all the data selected from the Dropdown List in Column B will be red circled as invalid data. Is there a solution for that? Thanks, Thomas
Glad this helped you Thomas. Can you clarify your question about column B though please - Do you want everything in column B highlighted as invalid? If so, you could use a conditional format formula like =NOT(ISBLANK(B5)) and apply it from B5 downwards with a red border format.
Is it possible to do this on a per column basis? So all the drop down options appear in full per column until you start assigning them to that specific column
👉 Up4Excel Downloads Library: »» ml.up4excel.com/library
✅ FREE Access to ALL Up4Excel Files. Includes Excel Templates, Training Workbooks, Example Data, Cheat Sheets and more. New Content Added Weekly!
Hi John. Nice one! If the user has Microsoft 365, then it can be done in one formula without helper columns. Using your template, enter the following in cell J5: =SORT(FILTER(D5:D21,NOT(COUNTIFS(B5:B21,D5:D21)))). Then for the range B5:B21, define Data Validation as a List and the Source is: =$J$5#. This will produce the same effect that you demonstrated. For every entry inside the range B5:B21, the FILTER function will spill the remaining entries which then become the available choices for the Data Validation list. Using the # character on the spilled range, makes the dropdown dynamic to whatever remains in the list. It is amazing what can be done with the new functions.. a real incentive for anyone using legacy EXCEL to upgrade and make their life easier. I hope someone finds this useful. Thanks for sharing your tips and tricks :)) Thumbs up!!
This is another excellent improvement suggestion Wayne. I've just tried it and it works a treat. Really clever use of COUNTIFS in there I wouldn't have thought of myself too. I'm really looking forward to getting some videos out there on Excel365 now I've been using it a bit, there's a load of stuff dynamic arrays facilitate that would take ages with conventional formulas - your comment is one example of many. Thanks for sharing 👍👍
@@Up4Excel Thanks John! This is another variation that is even cleaner: =SORT(FILTER(D5:D21,COUNTIFS(B5:B21,D5:D21)=0)). All the non-matches will evaluate to 0 and a test of = 0 then becomes TRUE which feeds back into FILTER. Fun to come up with different ways to get the solution :)) Thumbs up!!
@@wayneedmondson1065 Excellent, you're right that is even cleaner. Either way though, far easier with Excel365 👍
📗 Get Your Video Workbook Template Here: »» cutt.ly/up4v2005MFD
🎯 When it's Gone it's Gone!!…..This special dropdown list removes used items as they are choosen, and even puts them back if choices are changed. No one can make a duplicate choice with this automated decreasing dropdown list.
great job from great trainer
Thanks for the compliment Ahmed ...glad you liked it 👍
This is incredible. I want to try this with the new dynamic arrays
No doubt it'll be a whole lot simpler....they're a fantastic new Excel feature 👍
@Up4Excel This is very helpful! Would you have some instruction to get the dropdown list (column B in this video) to work on a completely different tab of the same excel document? Thank you!
Gald to help 👍 I think you can just link the dropdowns to anything, even on another sheet? You might have to adapt the formulas to include the sheet names.
Hi There, this is absolutely amazing. I copied your formulas courtesy of you and optimised our rosters and everyone loves it! Thank you very much!! Just one quick question if I may ask is that somehow all the data selected from the Dropdown List in Column B will be red circled as invalid data. Is there a solution for that? Thanks, Thomas
Glad this helped you Thomas. Can you clarify your question about column B though please - Do you want everything in column B highlighted as invalid? If so, you could use a conditional format formula like =NOT(ISBLANK(B5)) and apply it from B5 downwards with a red border format.
@@Up4Excel Awesome! I should have thought about conditional formatting. Thank you!!
@@thomassun6737 Great, hope it works for you 👍
Simply amazing!
Thanks Deepak, glad you think so. Hope you find it useful 👍
@@Up4Excel yes it's very helpful!
Is it possible to do this on a per column basis? So all the drop down options appear in full per column until you start assigning them to that specific column
I can't see why not Callum. It'd just be a case of setting up the system for each new column you want it on.