How to link the data from drop down lists

[NeedtolearnMore]

Good Afternoon,

Please note I'm very new to Excel..!

I have to create a master spread sheet that records the pallet number, the project names that are on each pallet and the serial numbers of the items that belong to each project.

I'm using column A to record pallet numbers. Pallet 1,2,3 etc. No problem so far.
I'm using a drop down box in column B to record the project name. Project Red, Blue, Green etc. I'm taking this data from sheet 2, inputting the information vertically in columns. No problem so far.
When I click the drop down list in column B and select a project that populates in the cell i.e. Project Blue, I then want a drop down list in column C to show me all of the serial numbers attached to that project, that I will be inputting in sheet 3 again vertically in columns.

I hope I have explained what I'm looking to do properly, I have tried to find a solution via Google but cant seem to find a similar scenario to mine.

Thank you for your time and patience!

 

[Perpa]

Need_TLM,
Welcome to the Forum. To learn more on this topic just search 'Dependent Drop Downs'.

On sheet3 you need to create some Named Ranges.
Create a named range called 'Project Color' and select the header cells on sheet3 in that row for each color,
ie. Project Red, Project Blue, Project Green, and etc.
Still on Sheet3, create a named range for each color, the ranges could be named SN_Red, SN_Blue, and etc.
The range for each SN will be first SN to the last SN in each column without the header (Project Red or whatever).

On the sheet where you do your drop downs, I assume Sheet1,
B2 is the cell on sheet1 where the named range 'Project Color' is the 'Source' for the drop down.
C2 is the cell on sheet1 where the Serial Number is selected from a drop down.
The 'Source' for the 'List' in C2 is the formula: '=indirect($B$2)' without the single quotes.


On the sheet where you do your drop downs, I assume Sheet1,
Right click sheet tab > View Code to see Sheet Change Event Macro in the VB Editor window that opens.
Copy the following macro right over the existing two lines of code…without the bracketed 'Code' line.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
  If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
  Range("C2").ClearContents
  Range("C2").Select
End Sub

Close the VB Editor window, then save the file as macro enabled.
When you go to sheet 1 and select a Project Color from the drop down in B2,
you will then select a Serial Number from the drop down in C2.


When you reopen the file, you may have to enable macros again to run the macro depending on which version of Excel you are using.
Give that a try.
Perpa