2 Variables determing a lookup sheet to use

[Neall]

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

 

[joel]

I added address into your formula with USA being a worksheet

=IF(ISERROR(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(D21:N21,USA),0))*P29),"",(INDEX(ADDRESS(C22:N41,USA),MATCH(C29,ADDRESS(A22:A41,USA),0),MATCH(IF(ISBLANK(leveloveride),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,ADDRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P29),"",(INDEX(ADDRESS(C22:N41,A1),MATCH(C29,ADDRESS(A22:A41,A1),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(D21:N21,A1),0))*P29))

 

[Neall]

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(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRESS(selectedgeo,selectedgeo),MATCH(C41,ADDRESS(GEOPART,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,selectedgeo),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?

 

[joel]

You are not consistant with the changes. This is wrong in 2 places

ADDRESS(selectedgeo,selectedgeo)

Should be

ADDRESS(GEOLEVEL,selectedgeo)

 

[Neall]

Thanks for that, however I am still getting no result.

=IF(ISERROR(INDEX(ADDRESS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,selectedgeo),0))*P41),"",(INDEX(ADDRESS(GEOPART,selectedgeo),MATCH(C41,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,selectedgeo),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

 

[joel]

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]

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,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK

(leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,selectedgeo),0))*P41)

I hope someone can help

Thanks

 

[Joel]

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,INDIRECT(selectedgeo&"Level"),0),

MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),INDIRECT(selectedgeo&"Level"),0))*P41),"",

(INDEX(INDIRECT(selectedgeo&"Part"),MATCH(C41,INDIRECT(selectedgeo&"Level"),0),

MATCH(IF(ISBLANK(leveloveride),Pricelevel,leveloveride),INDIRECT(selectedgeo&"Level"),0))*P41))