| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up dependent values in a list
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),MATCH("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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up dependent values in a list
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up dependent values in a list
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up dependent values in a list
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up dependent values in a list
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Looking up dependent values in a list
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sum values in one row dependent on a value in a different row | Excel Worksheet Functions | |||
| Create a list in a drop down dependent upon another dd list in exc | Excel Discussion (Misc queries) | |||
| Return a list dependent upon the selection of a preceeding list | Excel Worksheet Functions | |||
| How do I get a cell to count values from a list dependent on anoth | Excel Worksheet Functions | |||
| How do I create a dependent list, to a current list? | Excel Discussion (Misc queries) | |||
Linear Mode
