Using a dropdown list to input a relative ref

Morse - Jul 15, 2010 at 05:12 AM
 Trowa - Jul 15, 2010 at 08:55 AM

In Excel 2007 I am trying to use a validated dropdown list to input relative stored information.
For example, I have a number of different office's addresses stored in a sheet, which when I select the corresponding title from the dropdown list I want the address of the office entered in the relevant boxes underneath it for use with mail merge.

I have tried but failed, any help welcome.



1 reply

Hi Morse,

Take a look at my example and see if it works for you:
b	a	aa	aaa	aaaa
bb	b	bb	bbb	bbbb
bbb	c	cc	ccc	cccc

I used range B1:E3 to input some random data.
Cell A1 is used for dropdown menu (Data>Validate, select list and select range $B$1:$B$3.)
Cell A2 contains this formula: =VLOOKUP($A$1,$B$1:$E$3,2)
Drag the formula down to A4. Select cell A3 and change the last number of the formula (2) to 3.
Select cell A4 and change the last number to 4.

Now when you use the dropdown list to select the b the result will be bb, bbb, bbbb.

Hope this helps, if not, give an example of how your sheet looks like.

Best regards,