Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
Afternoon
I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
I added address into your formula with USA being a worksheet
=IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
Thanks I have made a few changes and cant seem to get it to work hoping you
can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
You are not consistant with the changes. This is wrong in 2 places
ADDRESS(selectedgeo,selectedgeo) Should be ADDRESS(GEOLEVEL,selectedgeo) "Neall" wrote: Thanks I have made a few changes and cant seem to get it to work hoping you can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
Thanks for that, however I am still getting no result.
=IF(ISERROR(INDEX(ADDRESS(GEOPART,selectedgeo),MAT CH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(I SBLANK(leveloveride),Pricelevel,leveloveride),ADDR ESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRE SS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL ,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pr icelevel,leveloveride),ADDRESS(GEOLEVEL,selectedge o),0))*P41)) Here is what I am trying to get USA worksheet part Level A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 So from the tally sheet if the person selects USA, Part number 2 Level C they will then get $6 back and multiply it by the number in cell P41 Should they choose CAN or UK it will take them to a different sheet and formatted exactly the same except they will be CANPART and CANLEVEL and give the price point for part 2 level C could be $10. Thanks for all your help in advance -- Neall "joel" wrote: You are not consistant with the changes. This is wrong in 2 places ADDRESS(selectedgeo,selectedgeo) Should be ADDRESS(GEOLEVEL,selectedgeo) "Neall" wrote: Thanks I have made a few changes and cant seem to get it to work hoping you can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
I don't know what you have GEOPART set to. You original data was starting in
at A22 your new data starts a A1. My guess is you have GEOPART set wrong or you have your data in the wrong location. "Neall" wrote: Thanks for that, however I am still getting no result. =IF(ISERROR(INDEX(ADDRESS(GEOPART,selectedgeo),MAT CH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(I SBLANK(leveloveride),Pricelevel,leveloveride),ADDR ESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRE SS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL ,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pr icelevel,leveloveride),ADDRESS(GEOLEVEL,selectedge o),0))*P41)) Here is what I am trying to get USA worksheet part Level A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 So from the tally sheet if the person selects USA, Part number 2 Level C they will then get $6 back and multiply it by the number in cell P41 Should they choose CAN or UK it will take them to a different sheet and formatted exactly the same except they will be CANPART and CANLEVEL and give the price point for part 2 level C could be $10. Thanks for all your help in advance -- Neall "joel" wrote: You are not consistant with the changes. This is wrong in 2 places ADDRESS(selectedgeo,selectedgeo) Should be ADDRESS(GEOLEVEL,selectedgeo) "Neall" wrote: Thanks I have made a few changes and cant seem to get it to work hoping you can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
Afternoon, my apologies for not responding I am hoping you can still help me
with this formula For clarity I have a defined name for the entire price sNo we didnt I was gojng to heet for lets say USA named USA, this has the part number running from A2 (A1 being the title) to A8 I have called that range USAPART and then from B1 - B7 I have the pricing levels I have named that range USALEVEL now I have 4 other sheets named Europe, Asia,Canada and SA which have all the same price sheet but their respectable part numbers columns and price levels prefix with the GEO they belong to (example EUROPPART,EUROPLEVEL etc) Now on the main sheet I have a formula which I have 2 cells suffixed with PART and LEVEL and depending on what GEO is chosen (USA, EUROPE etc) those cells get prefixed with the GEO so the cell then becomes USAPART or if Europe is seleceted is becomes EROUPPART and the same with level. From there once the correct GEO is determined pricing is then further determined by the defined price level multiplied by the number of parts being ordered (this I have working in the statement) What I now need to do is when a GEO is selected the correct pricing page is selected. and then the Pricelevel,leveloveride will take over and determine the price. I took out the error checking for now to remove some of the confusion =IF(INDEX(ADDRESS(GEOPART,selectedgeo),MATCH(C41,A DDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK (leveloveride),Pricelevel,leveloveride),ADDRESS(GE OLEVEL,selectedgeo),0))*P41) I hope someone can help Thanks -- Neall "joel" wrote: I don't know what you have GEOPART set to. You original data was starting in at A22 your new data starts a A1. My guess is you have GEOPART set wrong or you have your data in the wrong location. "Neall" wrote: Thanks for that, however I am still getting no result. =IF(ISERROR(INDEX(ADDRESS(GEOPART,selectedgeo),MAT CH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(I SBLANK(leveloveride),Pricelevel,leveloveride),ADDR ESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRE SS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL ,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pr icelevel,leveloveride),ADDRESS(GEOLEVEL,selectedge o),0))*P41)) Here is what I am trying to get USA worksheet part Level A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 So from the tally sheet if the person selects USA, Part number 2 Level C they will then get $6 back and multiply it by the number in cell P41 Should they choose CAN or UK it will take them to a different sheet and formatted exactly the same except they will be CANPART and CANLEVEL and give the price point for part 2 level C could be $10. Thanks for all your help in advance -- Neall "joel" wrote: You are not consistant with the changes. This is wrong in 2 places ADDRESS(selectedgeo,selectedgeo) Should be ADDRESS(GEOLEVEL,selectedgeo) "Neall" wrote: Thanks I have made a few changes and cant seem to get it to work hoping you can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
2 Variables determing a lookup sheet to use
You can get a named range using indirect instead of address
if the named range selectedgeo = USA then INDIRECT(selectedgeo&"Part") same as USAPART or INDIRECT(selectedgeo&"Level") same as USALevel =IF(ISERROR( INDEX(INDIRECT(selectedgeo&"Part"),MATCH(C41,INDIR ECT(selectedgeo&"Level"),0), MATCH(IF(ISBLANK(leveloveride),Pricelevel,levelove ride),INDIRECT(selectedgeo&"Level"),0))*P41),"", (INDEX(INDIRECT(selectedgeo&"Part"),MATCH(C41,INDI RECT(selectedgeo&"Level"),0), MATCH(IF(ISBLANK(leveloveride),Pricelevel,levelove ride),INDIRECT(selectedgeo&"Level"),0))*P41)) "Neall" wrote: Afternoon, my apologies for not responding I am hoping you can still help me with this formula For clarity I have a defined name for the entire price sNo we didnt I was gojng to heet for lets say USA named USA, this has the part number running from A2 (A1 being the title) to A8 I have called that range USAPART and then from B1 - B7 I have the pricing levels I have named that range USALEVEL now I have 4 other sheets named Europe, Asia,Canada and SA which have all the same price sheet but their respectable part numbers columns and price levels prefix with the GEO they belong to (example EUROPPART,EUROPLEVEL etc) Now on the main sheet I have a formula which I have 2 cells suffixed with PART and LEVEL and depending on what GEO is chosen (USA, EUROPE etc) those cells get prefixed with the GEO so the cell then becomes USAPART or if Europe is seleceted is becomes EROUPPART and the same with level. From there once the correct GEO is determined pricing is then further determined by the defined price level multiplied by the number of parts being ordered (this I have working in the statement) What I now need to do is when a GEO is selected the correct pricing page is selected. and then the Pricelevel,leveloveride will take over and determine the price. I took out the error checking for now to remove some of the confusion =IF(INDEX(ADDRESS(GEOPART,selectedgeo),MATCH(C41,A DDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK (leveloveride),Pricelevel,leveloveride),ADDRESS(GE OLEVEL,selectedgeo),0))*P41) I hope someone can help Thanks -- Neall "joel" wrote: I don't know what you have GEOPART set to. You original data was starting in at A22 your new data starts a A1. My guess is you have GEOPART set wrong or you have your data in the wrong location. "Neall" wrote: Thanks for that, however I am still getting no result. =IF(ISERROR(INDEX(ADDRESS(GEOPART,selectedgeo),MAT CH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(I SBLANK(leveloveride),Pricelevel,leveloveride),ADDR ESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRE SS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL ,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pr icelevel,leveloveride),ADDRESS(GEOLEVEL,selectedge o),0))*P41)) Here is what I am trying to get USA worksheet part Level A B C 1 $1 $2 $3 2 $4 $5 $6 3 $7 $8 $9 So from the tally sheet if the person selects USA, Part number 2 Level C they will then get $6 back and multiply it by the number in cell P41 Should they choose CAN or UK it will take them to a different sheet and formatted exactly the same except they will be CANPART and CANLEVEL and give the price point for part 2 level C could be $10. Thanks for all your help in advance -- Neall "joel" wrote: You are not consistant with the changes. This is wrong in 2 places ADDRESS(selectedgeo,selectedgeo) Should be ADDRESS(GEOLEVEL,selectedgeo) "Neall" wrote: Thanks I have made a few changes and cant seem to get it to work hoping you can help =IF(ISERROR(INDEX(ADDRESS(selectedgeo,selectedgeo) ,MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(I F(ISBLANK(leveloveride),Pricelevel,leveloveride),A DDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(AD DRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(G EOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,sele ctedgeo),0))*P41)) selectedgeo = a cell that is a variable depending on the country chosen so it can be USA,CAN,UK,AP which is then a defined name for the pricing area (used to be 'Value Unit Prices'!C22:N41) GEOLevel is a cell that puts the value from selectedgeo together with the word "LEVEL" which would = USALEVEL or CANLEVEL etc this is then a column on the corresponding pricing sheet that shows the price levels (used to be D21:N21) GEOPART is a cell that puts the value from selectedgeo together with the word "PART" which would = USAPART or CANPART etc this is then a row on the corresponding pricing sheet that shows the price levels (used to be A22:A41) So basically when selectedgeo cell is filled either automatically or overridden I am expecting it to go to the worksheet with the appropriatly defined name (USA,CAN...)then we can Also override the price level which is looked up using the part number associated to "(country)PART" and Price level ("country)Level" Did I miss something? -- Neall "joel" wrote: I added address into your formula with USA being a worksheet =IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,A DDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveri de),Pricelevel,leveloveride),ADDRESS(D21:N21,USA), 0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29, ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(levelover ide),Pricelevel,leveloveride),ADDRESS(D21:N21,USA) ,0))*P29)) You can make USA a cell location like A1 =IF(ISERROR(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,AD DRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride ),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0)) *P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDR ESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P 29)) "Neall" wrote: Afternoon I have a tally sheet that has 3 variables that can be changed one is GEO and the other is pricing level and part number. Each geo (USA,CAN, UK, AP) has the same pricing sheet outline with the pricing level left to right (A1 - A10) the part numbers (A2 - A 40) with the pricing in between. I currently have a statement that goes to one single pricing sheet and gets the data that I need which is =IF(ISERROR(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29),"",(INDEX('Value Unit Prices'!C22:N41,MATCH(C29,'Value Unit Prices'!A22:A41,0),MATCH(IF(ISBLANK(leveloveride), Pricelevel,leveloveride),'Value Unit Prices'!D21:N21,0))*P29)) 'Value Unit Prices' is the current locked pricing sheet, this needs to be a variable depending on what GEO the customer is from But now I need to add the variable of GEOs as the pricing changes per geography and this needs to be reflected in the price. what would I have to add to this above syntax to include the ability to choose the GEO which would then pull the pricing information from the corresponding sheet (example USA, CAN, UK, AP)? Any help would be greatly appreciated -- Neall |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup with two variables | Excel Discussion (Misc queries) | |||
Lookup with two variables | Excel Discussion (Misc queries) | |||
Lookup with Variables | Excel Worksheet Functions | |||
Determing last (used) row in a sheet | Excel Programming | |||
lookup 2 variables | Excel Discussion (Misc queries) |