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