Populate one drop down list based on another

[Tyson Couch]

I am working on a workbook to allow our techs to track serial numbers of the
equipment on there trucks.

The workbook only needs the model of the piece of equipment, the serial
number and the phone number of the account it was used on.

I have four columns.

First one is Model. The cells in it are populated from a Drop Down list I
made.
The second/third column is the serial number.
The serial number is split in to two sections. The first one is a drop down
list and the second column they enter the last 7 digits of the serial.
The reason I did this is because the first 8 do not change. It's just a
different set for each model.
The third is just the phone number of the work order.

My question is I want the second column to be auto selected based off of the
first column.
In other words right now we have two models of a receiver. MSSAT and DVR.
Mssat always starts with 4422D101.
So when I set a cell column 1 to Mssat, I would like it to auto select the
4422d101 in the same cell in column 2.
Is this possible?

I can post or email the excel file I have started if necessary.

 

[Debra Dalgleish]

In addition to the dropdown list in the second column, you could use a
formula to enter the serial number.

Create a table with each model and its serial number. Then, in cell B2:

=IF(A2="","",VLOOKUP(A2,ModelList,2,0))

Copy the formula down as far as required.

There's information on VLOOKUP here:

http://www.contextures.com/xlFunctions02.html

 

[RagDyer]

With only 2 models to choose from, you really don't need a second list.
A simple formula like this might work for you:

Drop down list in A5 -
Enter this in B5:

=IF(A5<>"",LOOKUP(A5,{"DVR";"MSSAT"},{"12345ABC";"4422D101"}),"")

And drag down to copy as needed.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"Tyson Couch" <tacouch at gmail dot com> wrote in message
I am working on a workbook to allow our techs to track serial numbers of the
equipment on there trucks.

The workbook only needs the model of the piece of equipment, the serial
number and the phone number of the account it was used on.

I have four columns.

First one is Model. The cells in it are populated from a Drop Down list I
made.
The second/third column is the serial number.
The serial number is split in to two sections. The first one is a drop down
list and the second column they enter the last 7 digits of the serial.
The reason I did this is because the first 8 do not change. It's just a
different set for each model.
The third is just the phone number of the work order.

My question is I want the second column to be auto selected based off of the
first column.
In other words right now we have two models of a receiver. MSSAT and DVR.
Mssat always starts with 4422D101.
So when I set a cell column 1 to Mssat, I would like it to auto select the
4422d101 in the same cell in column 2.
Is this possible?

I can post or email the excel file I have started if necessary.

 

[Tyson Couch]

Thank You

Debra and RagDyer.

I'm sure we'll be adding more models as they come out, so I think I'll use
Debra's method to plan for the future.

 

[Ragdyer]

Thanks for the feed-back.