View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Gord Dibben Gord Dibben is offline
external usenet poster
Posts: 22,906
Default drop down lists that populate other cells

On a new sheet enter in 3 columns your company names(A), street addresses(B),

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

On Sun, 11 Nov 2007 09:24:09 -0800, wrote:

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.