Try this:

=VLOOKUP(E20,INDIRECT(C20&"!A1:BH60"),MATCH(G20,IN DIRECT(C20&"!A1:BH1"),0),0)

--
Biff
Microsoft Excel MVP


"Bob Bob" wrote in message
...
I did changed the sheet names to try and keep it simple, sheet names are O
(style O), M (style M), A and so on.

The worksheet that I work in has cell C20 I input the style (O or M, or A
...) Cell E & G 20 I input numbers (sizes from column A and Row 1 from the
price sheets) I 20 is my formula that finds the price from the
corresponding style (worksheet), it then repeats on the next row

Style Column Row Price
O 12 15 $225.00
A 15 15 $305.00
.
Each time I add a new style I have to add on to the formula in cell I20
example new style to add is Z, First I add worksheet named Z and populate
it with prices, The on my worksheet I enter data in I go to cell F20 and
would add this to the end of the existing formula
"+(IF(C20=Z!DA$1,INDEX(Z!B$2:BH$60,MATCH(E20,Z!A$2 :A$60,0),MATCH(G20,Z!B$1:BH$1,0)),0))"Problem
is my Formula is getting really long, Was hoping to shorten it so Ican
just add the new style worksheet and not have to modify my formula
eachtime I add a new style(worksheet)."T. Valko"
wrote in l... Are your
*real* sheet names Sheet2, Sheet3, Sheet4 etc? I'm assuming C20 = lookup
style and SheetN!DA$1 holds the differentstyles? -- Biff Microsoft
Excel MVP "Bob Bob" wrote in
... I am using a formula on
one work sheet to find a price on other sheets sheet2 is formatted like
this a grid style price chart StyleO 12 15 12 $215.00
$225.00 15 $225.00 $235.00 18 $235.00 $246.00 Sheet3 is
styleM same formatt different prices and so on. I have 10different sheets
in total all the same format but will need at add new pricesheet from time
to time I want to shorten my formula from
this=(IF(C20=Sheet2!DA$1,INDEX(Sheet2!B$2:BH$60, MATCH(E20,Sheet2!A$2:A$60,0),MATCH(G20,Sheet2!B$1: BH$1,0)),0))+(IF(C20=Sheet3!DA$1,INDEX(Sheet3!B$2: BH$60,MATCH(E20,Sheet3!A$2:A$60,0),MATCH(G20,Sheet 3!B$1:BH$1,0)),0))+
... is there a way to shorten the formula so I don't have to add this
withthe new sheet number each time I add a new
sheet?+(IF(C20=Sheet2!DA$1,INDEX(Sheet2!B$2:BH$6 0,MATCH(E20,Sheet2!A$2:A$60,0),MATCH(G20,Sheet2!B$ 1:BH$1,0)),0))
Thanks to all who answer