Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8
Default drop down lists that populate other cells

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.

  #2   Report Post  
Posted to microsoft.public.excel.misc
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),
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

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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 347
Default drop down lists that populate other cells

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

" 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying Cells with Drop Down Lists Debby Bunce New Users to Excel 2 August 21st 06 12:17 AM
Copying Cells with Drop Down Lists Debby Bunce Excel Worksheet Functions 2 August 21st 06 12:17 AM
Auto populate several cells based on a selection from drop down li Sheldon Excel Discussion (Misc queries) 3 January 13th 06 09:12 PM
how do i set up drop down menus to populate cells in excel? Louise Scott Excel Discussion (Misc queries) 2 May 29th 05 07:19 PM
need to populate several cells based on drop down menu selection TerriF Excel Discussion (Misc queries) 1 May 18th 05 10:27 PM


All times are GMT +1. The time now is 10:49 PM.

Powered by vBulletin® Copyright ©2000 - 2023, Jelsoft Enterprises Ltd.
Copyright 2004-2023 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"