Data Validation - List - Based Upon a Cell's Value

Wulf

Active Member
Joined
Dec 1, 2004
Messages
384
Office Version
  1. 365
Platform
  1. Windows
Here's what I'm looking to do...

I have a list of 2500+/- Employees.

All employees have to go through specific training.

*BUT* the location for this training can change. Right now, there are 88 different PHYSICAL locations, on a different tab than the one the employees are on.

Piece of cake, right? Data Validation, List, enter the tab/range, select the location in column AI, no problem.

I've just been tasked to create an alternate list of locations, which would all be "Virtual"... 128 of them (Separate Conference Lines).

How can I make the validation list be based on whether the location will be Physical or Virtual?

IE: If the training type will be "Physical" (Cell AD2 - N2501, Drop-Down to select "Physical" or "Virtual" (A row for each employee)),
the data validation list has the drop-down for the 88 Physical locations, which will be in Column "AI", row 2 - 2501.

If the training type will be "Virtual" (Cell N2 - AD2501, Drop-Down to select "Physical" or "Virtual" (A row for each employee)),
the data validation list has the drop-down for the 128 Virtual locations, which will be in Column "AI", row 2 - 2501.

Is this possible, withOUT using any Macros? The file has to be loaded onto the OneDrive in Outlook, which does not support Macros.
1616799164036.png
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
If the dropdowns are in column AI, and the list of physical locations is Sheet2!$A$1:$A$88 and the list of virtual locations is in Sheet2!$B$1:$B128
Then you could select AI2 and define a name Name: mySource RefersTo = IF($AD2="Physical", Sheet2!$A$1:$A$88, Sheet2!$B$1:$B$128)

Then list validation with the source set to =MySource will work as you want.
 

Wulf

Active Member
Joined
Dec 1, 2004
Messages
384
Office Version
  1. 365
Platform
  1. Windows
"Then list validation with the source set to =MySource will work as you want."

I'm loving that this looks easy to do, however, I don't see anything anywhere in the Data Validation where I'm able to define a name Name: mySource.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,985
The name definiton is from the Define Name option on the Formula ribbon.
Once the name is defined, its used in the data Validation list.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,106
Messages
5,640,132
Members
417,126
Latest member
Jeffman52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top