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?

>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 Insert>Name>Define
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 Data>Validation
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" <Intuit.22kj5o_1138827305.6609@excelforum-nospam.com> wrote in
message news:Intuit.22kj5o_1138827305.6609@excelforum-nospam.com...
>
> 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
>

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.

Originally Posted by Intuit

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?

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

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" <Intuit.22lwp2_1138891559.9594@excelforum-nospam.com> wrote in
message news:Intuit.22lwp2_1138891559.9594@excelforum-nospam.com...
>
> 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
>

Originally Posted by Biff

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" <Intuit.22lwp2_1138891559.9594@excelforum-nospam.com> wrote in
message news:Intuit.22lwp2_1138891559.9594@excelforum-nospam.com...
>
> 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

Originally Posted by Biff

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" <Intuit.22lwp2_1138891559.9594@excelforum-nospam.com> wrote in
message news:Intuit.22lwp2_1138891559.9594@excelforum-nospam.com...
>
> 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!

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?

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