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

Wulf

Active Member
Joined
Dec 1, 2004
Messages
395
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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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.
 
Upvote 0
"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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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
Back
Top