Re: Looking up dependent values in a list

From: Frank Kabel (frank.kabel_at_freenet.de)
Date: 02/17/04 

Date: Tue, 17 Feb 2004 13:01:50 +0100

Hi John
if you like, mail me your spread*** 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" <frank.kabel@freenet.de> wrote in message
> news:evphOfU9DHA.3176@TK2MSFTNGP11.phx.gbl...
>> 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" <frank.kabel@freenet.de> wrote in message
>>> news:uSOokXT9DHA.2696@TK2MSFTNGP10.phx.gbl...
>>>> 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,Misc!$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
Loading