Hi John
if you like, mail me your spreadsheet and I'll look if I can setup your
formula

--
Regards
Frank Kabel
Frankfurt, Germany

John wrote:
Thats correct Frank, apples can be sourced from any location. I can
write the formula that returns the correct value (code) for the
combination apples/location. My problem starts when the "products"
that are selected are different from apples as they have a different
product/location code combination.

I have lists set up with locations and codes associated with those
locations. I have also a list set-up with Products and there
associated codes. Problem is that if it is a certain 'product' the
location detailed in the list is different. I can set up a list of
these codes but can't seem to combine both together in a formula

Thanks





"Frank Kabel" wrote in message
...
Hi John
as I understood it your problem arises then you have more than one
match in your product list. e.g. Apples from NY and London. What is
your espected result in this case for your target cell? (a
combination of both entires). And what should happen if you have
more than one match (e.g. 4 different locations)

--
Regards
Frank Kabel
Frankfurt, Germany

John wrote:
Thanks again Frank

I may have explained it incorrectly as a dependent list. All my
values are in lists but once a certain "value" is selected I want
it
to produce a value in another cell, thats why I was trying to use
the MATCH function - if that makes any sense

Rgds

John

"Frank Kabel" wrote in message
...
Hi
looks like you're wanting to create dependent listboxes for your
product entry. You may have a look at the following site for an
explanation how to create this kind of dependent listboxes:
http://www.contextures.com/xlDataVal02.html


--
Regards
Frank Kabel
Frankfurt, Germany

John D O'Connor wrote:
I am trying to right a formula that will An example may explain
better:

I have a table with the (Products) following in Row order, say A1
through A4. Each one of these has its own code depending on where
they are purchased from

Apples
Oranges
Bananas
Pears

These can be purchased from a large number of Location. For
simple
example purposes I will just have 2 locations:

New York - the code for which can either be NY01X or NY01
depending on the product selected
London - the code for which can either be LN01X or NY01 depending
on
the product selected

I have a Drop down list with the products listed. I choose one of
the 4 products above and that should produce a code for me. It
does to a point. I can't get it to return a either **01 or **01X
but not both using a set formula in the cell. This is required
because any product can be selected.

Apples in New York should have the code 2000-NY01
Oranges in New York should have the code 2050-NY01X
Pears in London should have the code 2100-LN01
Pears in New York should have the code 2100-LN01X

My attempt at the fomula was the following: - please ignore
actual
references in this formula as its from actual data:

=IF(C11=0,"
",INDEX(Misc!$I$47:$K$75,MATCH($C11,Misc!$I$47:$I$ 75),MATCH("Sub
Account",Misc!$I$47:$K$47,)))


In this C11 is the location where 'Sub Account' lists all the
codes with **01. I47:K47 is the whole array where all codes are
displayed

My second attempt was to try and combine both 'SubAccount' list
and the list the details the values **01X by the following-




=IF(K10=14100,(INDEX(Misc!$I$47:$L$75,MATCH($C10,M isc!$I$47:$I$75),MATC
H("Su
b L3",Misc!$I$47:$L$47,)))),IF(C10=0,"
",INDEX(Misc!$I$47:$K$75,MATCH($C10,Misc!$I$47:$I$ 75),MATCH("Sub
Account",Misc!$I$47:$K$47,)))

But this only produces a #value. I tried to put an @if statement
at the start, this returns the correct value but only if the
second
@if
statement that is in the formula shown is not put in.

Sorry if this seems rather complicated

Thanks

John