drop down lists that populate other cells

[Danie.Bright]

I need some help with an Excel spreadsheet.

So however you can make this work - i'll try.

Basically I have a drop down list/box in B11 with my customers names,
(the simple drop down box i can do) - but i want to select a company
from the drop down box in B11 and the sheet to automatically populate
the customers information in subsequent cells. For example. In B11 I
choose Company A. than elsewhere on the worksheet this selection
should trigger text to appear in J11 (the company's name again:
Company A), J12 (the company's address: 123 Main St.), and J13 (the
city informaton: Charlotte, NC 20000).

I couldnt figure out how to do this so i was going to opt for a two
drop down boxes, a simple one in B11-just easily choosing the
company's name to fill the blank, and THEN a complex one in J11 (which
would populate to two cells below it). However i cannot figure out to
make it so a selection in the drop down in J11 will also populate the
necessary information in J12 & J13.

So then i was trying to figure an "IF" type function, if B11 =
"Company A", then B12 = "123 Main St." and B13 = "Charlotte, NC
20000". I could not make it do anything remotely similar to this.

I have Macro's applications working elsewhere in this worksheet as
well for much simplier tasks, but i am concerned with doing this for
these addresses because there are upwards of 300 different company's &
address to assign a Macro's to, and it just seems like there is an
easier way to go about this.

Finally, i have come across this VisualBasics (or something like
that), while trying to research how to do this - but i know nothing
about this...sooo....


please help if you can.

 

[Gord Dibben]

On a new sheet enter in 3 columns your company names(A), street addresses(B),
city/state(C).

Give this range a name like mytable.

Select the company column range and give it a name of company

Back to first sheet.

In J11 enter =VLOOKUP($B11,mytable,1,FALSE)

In J12 enter =VLOOKUP($B11,mytable,2,FALSE)

In J13 enter =VLOOKUP($B11,mytable,3,FALSE)

To error trap for #N/A the above could be entered as

=IF(ISNA(VLOOKUP($B11,mytable,1,FALSE)),"",VLOOKUP($B11,mytable,1,FALSE))

Pick a company from your dropdown list.


Gord Dibben MS Excel MVP

 

[Guest]

Hi Danie

You need to create a table somewhere in your workbook, the first column
should be company names, fill the other columns with the rest of the
information in the order you want them to be displayed
(name,address,city,phone...etc). Once that is done you can use a VLOOKP
formula to retrieve the information depending on the company name you select
in your drop down menu.

in J11 type:
=B11

in J12 type:
=IF(J11="","",VLOOKUP($J11,table_range,COLUMN(B1),0))
copy across as far as needed.

table_range is where you created your table, so if your table is in sheet2
from A1 to D50 then the formula would read:

=IF(J11="","",VLOOKUP($J11,Sheet2!$A$1:$D$50,2,0))
adjust to suit!

HTH
Jean-Guy