PullDown List with two Columns help!

[chris100]

Hi all,

This is either incredibly easy problem or very hard - i've searched for an answer here and elsewhere but with no luck.

Basically, i need a pull down list that references two columns but only uses the data in the first column for data entry.
The reason is i may have the same products from different suppliers so need to differentiate between them.

So in case i didn't explain clearly enough - First column in the pulldown list would be "Product", second column would be "Supplier." Both would be visible in the pull-down list, only the product would be entered in the cell.

Any help would be much appreciated, as always,

Thanks,

Chris

[Ron Coderre]

Start by checking the Dependent Lists section of Debra Dalgleish's website:

http://www.contextures.com/xlDataVal02.html


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"chris100" wrote:

>
> Hi all,
>
> This is either incredibly easy problem or very hard - i've searched for
> an answer here and elsewhere but with no luck.
>
> Basically, i need a pull down list that references two columns but only
> uses the data in the first column for data entry.
> The reason is i may have the same products from different suppliers so
> need to differentiate between them.
>
> So in case i didn't explain clearly enough - First column in the
> pulldown list would be "Product", second column would be "Supplier."
> Both would be visible in the pull-down list, only the product would be
> entered in the cell.
>
> Any help would be much appreciated, as always,
>
> Thanks,
>
> Chris
>
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=499022
>
>

[Paul W Smith]

Cannot offer any examples, am sure others here can.

I can tell you that you should research the 'bound' column.

You can set the number of columns shown in the properties of your control.

I always have trouble loading multicolumn list boxes!!!!!

HTH a little.


"chris100" <[email protected]> wrote in
message news:[email protected]...
>
> Hi all,
>
> This is either incredibly easy problem or very hard - i've searched for
> an answer here and elsewhere but with no luck.
>
> Basically, i need a pull down list that references two columns but only
> uses the data in the first column for data entry.
> The reason is i may have the same products from different suppliers so
> need to differentiate between them.
>
> So in case i didn't explain clearly enough - First column in the
> pulldown list would be "Product", second column would be "Supplier."
> Both would be visible in the pull-down list, only the product would be
> entered in the cell.
>
> Any help would be much appreciated, as always,
>
> Thanks,
>
> Chris
>
>
> --
> chris100
> ------------------------------------------------------------------------
> chris100's Profile:
> http://www.excelforum.com/member.php...o&userid=25166
> View this thread: http://www.excelforum.com/showthread...hreadid=499022
>

[chris100]

Had a look but although useful, is not quite what i need here. I'll give an example:

PRODUCT SUPPLIER
A E
B F
C G
D H

The pull down list should have A next to E, B next to F etc.

[Ron Coderre]

It seems like each Product has only 1 Supplier....is that true?

If Yes, then maybe you could get by with a lookup list.

Put a new worksheet in your workbook, then:
A1: Product
B1: Supplier
A2: Prod_A (or whatever products you have)
B2: Prod_A_Supplier
Continue filling in the list

When done...
Select from A2 through the last item in Col_B
Insert>Name>Define
Name in workbook: LU_ProdSupplier
Refers to: (your already selected list)
Click the [OK] button

Next, switch to your input sheet
Select the Product input cells
Data>Validation
Allow: List
Source: =OFFSET(LU_ProdSupplier,0,0,,1)
Click the [OK] button

Select the Supplier cells (I'll assume they begin in cell B2)
B2: =IF(ISBLANK(A2),"",VLOOKUP(A2,LU_ProdSupplier,2,0))
Copy that formula down as far as you need.

Results:
The Product input cells will only allow valid products.
The Supplier cells will lookup each product in the LU_ProdSupplier list and return the corresponding supplier.

Is that something you can work with?

Regards,
Ron

[chris100]

I wish one product only had one supplier! No, unfortunately one product could have many suppliers, so the pull down list i need changes regularly.

I'm still at early stages with this one - will post when i have any fresh ideas or answers.

[droopy928gt]

This might be a dum one as I'm also only a rookie on this Excel VBA programming. VB is no problems at all but Excel VBA has already caused me several headaches.

I had similar but simply used 2 pulldown lists. You could do same where you select the product from one and the supplier from the other or vice versa whichever is more important.

In my case I had all products in one and all suppliers in the other. As soon as I selected a product or a supplier code in either code made sure that the items from the other list only displayed suppliers for selected product or products for selected supplier which then in turn could be selected in the second step.

Above is also relatively easy to do.

Best regards,

Leon

[rethin]

Little roundabout....

Create a third field and concatenate both Product and Supplier into the
field.

In the input section, provide the third column range as the validation
range.

Once you selected the product&supplier combination, you can extract the
product code alone using the mid() function of Excel.

If you still have thisp problem open, please send me a mail...I can
send you a template.

[chris100]

Thanks for all the help. In the end i did create a third column which was a combination of whichever items were chosen. So, if apples and supplier Bob were selected, the new column would give apples.bob. I then used this new list to create the pull down.

=C45&"-"&D45 (C45 having products and D45 Suppliers)

If anyones confused i can explain more... but right now i'm in a rush.

Again, thanks for all the help,

Chris