Help to shorten a formula
 

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

Help to shorten a formula
 

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

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

--
Biff
Microsoft Excel MVP


"Bob Bob" wrote in message
...
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

Help to shorten a formula
 

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

Help to shorten a formula
 

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

Help to shorten a formula
 

thank you very much it worked like a charm

"T. Valko" wrote in message
...
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

Help to shorten a formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Bob Bob" wrote in message
...
thank you very much it worked like a charm

"T. Valko" wrote in message
...
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