Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Hello again. I received some excellent assistance from paul B last time
I was here and am hoping to get some help again. I have set up a
spreadsheet that requires info to be inputted into cells. I have
created drop down lists in the cells to only allow certain information
to be inputted. I would like to do a couple things but can't figure
out how or even how to look it up in help. I would like to:

Allow user to use a drop down list and pick the appropriate entry, then
have another drop down list in the adjacent cell that only contains
items that correspond to what the first drop down entry was (i.e.
company name in first drop down box, adjacent cell drop down list only
contains parts made for that company). Then I would like the next
column to contain the pricing of the item that was chosen in the
previous cell's drop down box. Ideally, this cell would be populated
automatically depending on the part chosen for the company. I am
trying to create a log of parts shipped and total amount shipped.
Hopefully this wasn't too confusing! Any help would be greatly
appreciated. Thanks!!!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Also, I would like to populate the lists automatically.


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Limit drop down list and linking to other info

Hi!

Here's a minimal sample file that demonstrates one way to do this:

http://s48.yousendit.com/d.aspx?id=1...P0UJQKCTK8LVNL

Make a list of the unique company names and use that list as the source for
the company drop down. In the sample file that list is in the range G2:G6.

Make a 3 column table that lists the company name, part numbers and price.

The source for the dependent drop down for the part numbers is derived from
the above list. Select cell B2 then goto DataValidation. You'll see the
formula that is used to create the range for the dependent drop down.

Then use a simple Vlookup to get the price. I guess it's possible for
multiple companies to have the same part numbers. If you find that that's
the case we need to use a different formula to get the price.

Biff

"Intuit" wrote in
message ...

Hello again. I received some excellent assistance from paul B last time
I was here and am hoping to get some help again. I have set up a
spreadsheet that requires info to be inputted into cells. I have
created drop down lists in the cells to only allow certain information
to be inputted. I would like to do a couple things but can't figure
out how or even how to look it up in help. I would like to:

Allow user to use a drop down list and pick the appropriate entry, then
have another drop down list in the adjacent cell that only contains
items that correspond to what the first drop down entry was (i.e.
company name in first drop down box, adjacent cell drop down list only
contains parts made for that company). Then I would like the next
column to contain the pricing of the item that was chosen in the
previous cell's drop down box. Ideally, this cell would be populated
automatically depending on the part chosen for the company. I am
trying to create a log of parts shipped and total amount shipped.
Hopefully this wasn't too confusing! Any help would be greatly
appreciated. Thanks!!!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298



  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Great! I think that example will help. I think I understand what you
put in, but can I have my 3 column table on another worksheet within
the same workbook?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Limit drop down list and linking to other info

can I have my 3 column table on another worksheet
within the same workbook?


Yes, but that will require some changes.

Assume the lookup table is on Sheet2.

A1, B1 and C1 are the column headers.

The actual table data is in the range A2:C20.

Goto InsertNameDefine
Names in workbook: Parts (or whatever you want to call it but Parts seems
self explanatory to me!)
Refers to:

=OFFSET(Sheet2!$A$2,MATCH(Sheet1!$A$2,Sheet2!$A$2: $A$20,0)-1,1,COUNTIF(Sheet2!$A$2:$A$20,Sheet1!$A$2))

OK out

Then select cell B2 on Sheet1 (the dependent drop down)
Goto DataValidation
In the Source box, delete the formula that is currently entered and replace
it with this:

=Parts

OK out

This is all necessary because Excel doesn't "like" it when you use
validation that is located on a different sheet!

Biff

"Intuit" wrote in
message ...

Great! I think that example will help. I think I understand what you
put in, but can I have my 3 column table on another worksheet within
the same workbook?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298





  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Biff,
That worked great! Thanks! However, I have something even more
challenging now. Some part names don't have part numbers and vice
versa. Some also have both. How would I tweak these formulas so that
if there is a part number and name, all I have to do is pull down the
drop down list and pick the part number and the part name automatically
appears in the next column along with the price in the next column after
that. If there is no part number, than I would like to be able to go to
the next column and pick a part number and then the price be
automatically entered in. The drop down list in the part name column
would once again need to be populated only with parts pertaining to
that company. I know this is alot of info I'm asking for and probably
pretty confusing as well. I have learned alot already, but it seems
like once one obstacle is completed, another pops up. Thanks in
advance for all your help.


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Intuit Wrote:
Biff,
That worked great! Thanks! However, I have something even more
challenging now. Some part names don't have part numbers and vice
versa. Some also have both. How would I tweak these formulas so that
if there is a part number and name, all I have to do is pull down the
drop down list and pick the part number and the part name automatically
appears in the next column along with the price in the next column after
that. If there is no part number, than I would like to be able to go to
the next column and pick a part number and then the price be
automatically entered in. The drop down list in the part name column
would once again need to be populated only with parts pertaining to
that company. I know this is alot of info I'm asking for and probably
pretty confusing as well. I have learned alot already, but it seems
like once one obstacle is completed, another pops up. Thanks in
advance for all your help.


Well I figured out a way around my dillema above, however, I'm still
having one small problem. I can't get my drop down list to change
correctly with cells below the first row. The formula you showed me
worked great for the first row, but when I try and use it on the cell
below it, the drop down list has some of the correct parts on it, and
some from other companies??? Any idea why? Also, what did the -1 and
1 part of the formula you type mean?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Limit drop down list and linking to other info

Ok, I'm not really following you on this!

So, it sounds like you have 4 data elements:

1. company name
2. part name
3. part number
4. price

If you have the company name and the part name but do not have that part
names part number, where do you get that part number?

Can you post a sample file that shows how this should work and what it looks
like?

Biff

"Intuit" wrote in
message ...

Biff,
That worked great! Thanks! However, I have something even more
challenging now. Some part names don't have part numbers and vice
versa. Some also have both. How would I tweak these formulas so that
if there is a part number and name, all I have to do is pull down the
drop down list and pick the part number and the part name automatically
appears in the next column along with the price in the next column after
that. If there is no part number, than I would like to be able to go to
the next column and pick a part number and then the price be
automatically entered in. The drop down list in the part name column
would once again need to be populated only with parts pertaining to
that company. I know this is alot of info I'm asking for and probably
pretty confusing as well. I have learned alot already, but it seems
like once one obstacle is completed, another pops up. Thanks in
advance for all your help.


--
Intuit
------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298



  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


I found out that the each successive cell adds one extra part to the
populated drop down list. So by the 4th cell, (my first is fine), I
have 3 additional parts on the populated list that shouldn't be
there...


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Biff Wrote:
Ok, I'm not really following you on this!

So, it sounds like you have 4 data elements:

1. company name
2. part name
3. part number
4. price

If you have the company name and the part name but do not have that
part
names part number, where do you get that part number?

Can you post a sample file that shows how this should work and what it
looks
like?

Biff

"Intuit" wrote
in
message ...

Biff,
That worked great! Thanks! However, I have something even more
challenging now. Some part names don't have part numbers and vice
versa. Some also have both. How would I tweak these formulas so

that
if there is a part number and name, all I have to do is pull down

the
drop down list and pick the part number and the part name

automatically
appears in the next column along with the price in the next column

after
that. If there is no part number, than I would like to be able to go

to
the next column and pick a part number and then the price be
automatically entered in. The drop down list in the part name

column
would once again need to be populated only with parts pertaining to
that company. I know this is alot of info I'm asking for and

probably
pretty confusing as well. I have learned alot already, but it seems
like once one obstacle is completed, another pops up. Thanks in
advance for all your help.


--
Intuit

------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread:

http://www.excelforum.com/showthread...hreadid=507298


Ok, I'm not sure how to post files but I will try and clarifiy my
spreadsheet. I have one worksheet that will be our shipping log. this
contains the drop down menus. I have a column on this for company, part
number, part name, price etc. The company column has a drop down menu
that refers to another worksheet that only contains unique company
names. The part name column's drop down list on the shipping log
references another worksheet that has 4 columns: company name, part
number, part name, and price. There may be several instances of a
company name in this worksheet because of the many different parts made
for that company. So I would like the drop down menu for the cells in
the part number column on the shipping log to only contain parts for
the one particular company. The solution you gave works for the first
cell. When I go to the next cell down, it contains all the part
numbers for the previous entry's company name. I somewhat fixed this
by taking out some of the $ signs in your formula. However, now, each
successive entry results in one extra part being added to the populated
list that is supposed to only contain parts for that company. I hope
this clarified things. If not, I will try and figure out how to a
picture of my spreadsheet


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298



  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


Biff Wrote:
Ok, I'm not really following you on this!

So, it sounds like you have 4 data elements:

1. company name
2. part name
3. part number
4. price

If you have the company name and the part name but do not have that
part
names part number, where do you get that part number?

Can you post a sample file that shows how this should work and what it
looks
like?

Biff

"Intuit" wrote
in
message ...

Biff,
That worked great! Thanks! However, I have something even more
challenging now. Some part names don't have part numbers and vice
versa. Some also have both. How would I tweak these formulas so

that
if there is a part number and name, all I have to do is pull down

the
drop down list and pick the part number and the part name

automatically
appears in the next column along with the price in the next column

after
that. If there is no part number, than I would like to be able to go

to
the next column and pick a part number and then the price be
automatically entered in. The drop down list in the part name

column
would once again need to be populated only with parts pertaining to
that company. I know this is alot of info I'm asking for and

probably
pretty confusing as well. I have learned alot already, but it seems
like once one obstacle is completed, another pops up. Thanks in
advance for all your help.


--
Intuit

------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread:

http://www.excelforum.com/showthread...hreadid=507298


To get my part name where this is no number and vice versa, I'm just
entering the same in both columns on my part list worksheet. Than I do
a VLookup to populate the cell with the missing information on the
shipping log automatically. I think I have that worked out, but the
drop down list thing is stumping me for sure...as is how to post a
sample spreadsheet! Please don't shoot the Noob! My sample ss in word
is too big to post!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #12   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


I have another problem. how do I get vlookup to return info in a column
that is not directly adjacent to the column its looking up in?


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #13   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Intuit
 
Posts: n/a
Default Limit drop down list and linking to other info


I got it. Thanks for all your help Biff. I'm sure you will see me
again!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile: http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298

  #14   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Biff
 
Posts: n/a
Default Limit drop down list and linking to other info

OK, good deal!

I'm sure you will see me again!


"We're" always open!

Biff

"Intuit" wrote in
message ...

I got it. Thanks for all your help Biff. I'm sure you will see me
again!


--
Intuit
------------------------------------------------------------------------
Intuit's Profile:
http://www.excelforum.com/member.php...o&userid=30901
View this thread: http://www.excelforum.com/showthread...hreadid=507298



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
Ooh .. Linking a list to a list to an output cell StrawDog Excel Discussion (Misc queries) 4 August 22nd 05 09:51 PM
Drop down box with country list john liem New Users to Excel 0 May 31st 05 06:14 PM
Linking Drop-down list to worksheets Compnerd Excel Discussion (Misc queries) 0 March 7th 05 07:13 PM
Linking List Bottle Excel Worksheet Functions 3 December 20th 04 06:41 PM


All times are GMT +1. The time now is 11:40 AM.

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

About Us

"It's about Microsoft Excel"