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

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" 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

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?

--
Neall


"joel" wrote:

> 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" 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

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(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?
>
> --
> Neall
>
>
> "joel" wrote:
>
> > 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" 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

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

--
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(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?
> >
> > --
> > Neall
> >
> >
> > "joel" wrote:
> >
> > > 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" 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

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),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
>
> --
> 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(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?
> > >
> > > --
> > > Neall
> > >
> > >
> > > "joel" wrote:
> > >
> > > > 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" 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

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






--
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),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
> >
> > --
> > 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(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?
> > > >
> > > > --
> > > > Neall
> > > >
> > > >
> > > > "joel" wrote:
> > > >
> > > > > 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" 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

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))


"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,ADDRESS(GEOLEVEL,selectedgeo),0),MATCH(IF(ISBLANK
>
> (leveloveride),Pricelevel,leveloveride),ADDRESS(GEOLEVEL,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),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
> > >
> > > --
> > > 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(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?
> > > > >
> > > > > --
> > > > > Neall
> > > > >
> > > > >
> > > > > "joel" wrote:
> > > > >
> > > > > > 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" 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