Help to shorten a formula

[Bob Bob]

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 10
different sheets in total all the same format but will need at add new price
sheet 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,Sheet3!B$1:BH$1,0)),0))+
....

is there a way to shorten the formula so I don't have to add this with the
new sheet number each time I add a new sheet?
+(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))

Thanks to all who answer

 

[T. Valko]

Are your *real* sheet names Sheet2, Sheet3, Sheet4 etc?

I'm assuming C20 = lookup style and SheetN!DA$1 holds the different styles?

 

[Bob Bob]

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" <[email protected]> wrote in messageAre 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" <[email protected]> wrote in messageam 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,Sheet3!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$60,MATCH(E20,Sheet2!A$2:A$60,0),MATCH(G20,Sheet2!B$1:BH$1,0)),0))>>>> Thanks to all who answer>>>>>>

 

[T. Valko]

Try this:

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

 

[Bob Bob]

thank you very much it worked like a charm

Try this:

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

 

[T. Valko]

You're welcome. Thanks for the feedback!