Thanks for this tip! I think I have managed to improve it a little by including in the "Managers" list a jump to the "AllEmployees" list. Also, I supplemented the list names with down arrows so it resembles the look in your introduction. The condition thus became: =IF(B1="Managers ▼",Managers,AllEmployees)
@@OfficeinstructorThanks! Unfortunately, I discovered that the formula does not work as intended. This applies to both the original formula and my extension. Problems arise after a selection of a manager is made and then another manager is selected. Then the Employees list is displayed instead of the Managers list! This is because the condition B1="Managers" is not fulfilled when a manager has been selected. I have not figured out how to fix this in the original solution, but I have managed to adapt my solution so that it is now: =IFS(B1="Managers ▼",Managers, B1="Employees ▼",AllEmployees, ISNA(MATCH(B1,Managers,0)),AllEmployees, TRUE,Managers). (I hope I got a correct translation from my Swedish Excel.) Maybe is there a better way to solve it? If it is unclear which list the selected value comes from, a header can be created with this formula: =IF(COUNTIF(Managers,B2),"Managers","Emplyees") (Since the Data Validation has been moved down one line, in its formula B1 must be changed to B2.)
You can create multiple drop lists in one single cell. Here is a link to a tutorial in which I explain how to create 5 drop lists in a single cell and be able to switch between them.
Thanks for this tip! I think I have managed to improve it a little by including in the "Managers" list a jump to the "AllEmployees" list. Also, I supplemented the list names with down arrows so it resembles the look in your introduction. The condition thus became: =IF(B1="Managers ▼",Managers,AllEmployees)
Nice work!
@@OfficeinstructorThanks! Unfortunately, I discovered that the formula does not work as intended. This applies to both the original formula and my extension. Problems arise after a selection of a manager is made and then another manager is selected. Then the Employees list is displayed instead of the Managers list! This is because the condition B1="Managers" is not fulfilled when a manager has been selected. I have not figured out how to fix this in the original solution, but I have managed to adapt my solution so that it is now:
=IFS(B1="Managers ▼",Managers, B1="Employees ▼",AllEmployees, ISNA(MATCH(B1,Managers,0)),AllEmployees, TRUE,Managers).
(I hope I got a correct translation from my Swedish Excel.) Maybe is there a better way to solve it?
If it is unclear which list the selected value comes from, a header can be created with this formula: =IF(COUNTIF(Managers,B2),"Managers","Emplyees") (Since the Data Validation has been moved down one line, in its formula B1 must be changed to B2.)
Didn't even know this was possible, but see a great use. Could you nest multiple IF statements? Say to change to different teams or departments.
You can create multiple drop lists in one single cell. Here is a link to a tutorial in which I explain how to create 5 drop lists in a single cell and be able to switch between them.
you are simply superb, never thought such a thing is possible
Glad you liked it
Thank you Nabil for this great tutorial:) nice to see u back
Thank you
Very good Nabil! I like this! Thank you.
Thank you too
Amazing and very useful trick, thanks for share!!
Glad you liked it!
Please add Ms. Access Tips & Tricks videos in your education program .... still Awaiting ... ;)
No plans for Access for the time being
Genius!!!!
Thank you
Amazing trick
Thanks
Enjoy
Thanks
Welcome
Excellent logic my friend. Thanks for sharing!
Thanks for watching!
Brilliant. Thx 🙏
You're most welcome!