Drop Down List

[DJFANDANGO]

Good Day my brave excell'ers!

I have a sheet (Sheet2) with the list below

[Sheet2]

	a	b	c
1	Bob	obs	tech
2	mark	nav	elec
3	john	tac	mech

<tbody>
</tbody>

On [Sheet1] I'd like to have a Drop-Down list in A1, then when a name is selected it populates (B and C) with the relevant information... is this dooable? (is that even a word...?)

[Sheet1]

	a	b	c
1	[NAME Drop-Down][$A1]	Populate from $B1	Populate from $C1
2
3

<tbody>
</tbody>

Many thanks for the help!
Steve

 

[lrobbo314]

Re: Drop Down List Help

Create a named range on sheet 2 with this formula.

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A))

Then in A1 on Sheet1, add data validation, choose 'List', and for the source write '=Names'.

Then in cells B1 and C1 on Sheet1, enter the following formulas respectively.

=IF(A1="","",INDEX(Sheet2!B1:B3,MATCH(A1,Sheet2!A1:A3,0)))

=IF(A1="","",INDEX(Sheet2!C1:C3,MATCH(A1,Sheet2!A1:A3,0)))

 

[DJFANDANGO]

Re: Drop Down List Help

Lrobbo314,

Thanks for that, maybe I'm not grasping the first part, I have selected the name 'range' in 'Sheet2' and called it [Names], where do i place the '=offset code?

Many thanks

 

[lrobbo314]

Re: Drop Down List Help

You go to the formulas tab on the Ribbon and select 'Name Manager'. Then click 'New'. It'll ask you for a name and a formula. The name should be 'Names', and the formula is the offset code. This will give you a dynamic named range, meaning that as you add names to the list your drop down will be updated, that you can use to make your dropdown list. Then, you select the cell that you want to have the drop down list, click on data validation, select 'List', and for 'Source' you type '=Names'.

 

[Peter_SSs]

Re: Drop Down List Help

Another approach.
On Sheet2, insert headings above the data you have shown (it's unusual not to have headings for data like that anyway).
Select the headings and data on Sheet2 and on the Insert ribbon tab choose Table & tick My table has headers.

Excel Workbook
	A	B	C
1	Name	Data 1	Data 2
2	Bob	obs	tech
3	mark	nav	elec
4	john	tac	mech
Sheet2

On Sheet1 select cell A1
Data ribbon tab -> Data Validation drop-down arrow -> Data Validation... -> Settings tab -> Allow: List -> Source: =INDIRECT("Table1[Name]") -> OK (Note: If you have other tables then check or rename the Sheet2 table in the Name Manager on the Formulas ribbon tab)
Formula in B1 is copied across (& down if necessary)

Excel Workbook
	A	B	C
1	john	tac	mech
Sheet1

#VALUE!

 

[DJFANDANGO]

Re: Drop Down List Help

Hi Peter,

Got it working! Thanks a bunch!

Steve

 

[Peter_SSs]

Re: Drop Down List Help

Hi Peter,

Got it working! Thanks a bunch!

Steve

You're welcome.