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

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

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" wrote in
message ...

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

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.


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=499022

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
InsertNameDefine
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
DataValidation
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


--
Ron Coderre
------------------------------------------------------------------------
Ron Coderre's Profile: http://www.excelforum.com/member.php...o&userid=21419
View this thread: http://www.excelforum.com/showthread...hreadid=499022

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.


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=499022

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


--
droopy928gt
------------------------------------------------------------------------
droopy928gt's Profile: http://www.excelforum.com/member.php...o&userid=30232
View this thread: http://www.excelforum.com/showthread...hreadid=499022

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.

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


--
chris100
------------------------------------------------------------------------
chris100's Profile: http://www.excelforum.com/member.php...o&userid=25166
View this thread: http://www.excelforum.com/showthread...hreadid=499022