Dynamic Drop Down Lists Availability Across Mutliple Sheets

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
497
Office Version
  1. 2013
Platform
  1. Windows
I am trying to create dynamic drop down lists based on the value of another cell. Essentially, there are 2 columns impacted:
A1 = Item ID
B1 = Type
If I select Apple in the drop down list in B2, I would like the options in A2 to reflect values from a sheet called Apples.
If I select Cars in the drop down list in B2, I would like the options in A2 to reflect values from a sheet called Cars.

I'm thinking that I might need to use VBA for this, but I really would prefer not to, since I don't need VBA for anything else in this workbook.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
You will need a macro to do what you want. In which column and range in sheets "Apples" and "Cars" will the values be found?
 

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
497
Office Version
  1. 2013
Platform
  1. Windows
The information I would want to populate from the Apples sheet would be in column B, and the information on the Cars sheet would be in column C.
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
Are those the only two sheets? If not, what are the columns in the additional sheets? Do the values start in row 1?
 

reberryjr

Active Member
Joined
Mar 16, 2017
Messages
497
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

No, there are other sheets in the workbook, and the values would start in row 2 of each respective sheet.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
48,246
Office Version
  1. 365
Platform
  1. Windows
You can do that with dependant data validation
 

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
9,745
If the columns containing the values are different in each sheet, we would need to know the sheet names and the corresponding columns. It would be easier if the values were all in the same column on each sheet.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,261
Messages
5,546,832
Members
410,759
Latest member
Bufnercash
Top