Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shorten Formula | Excel Discussion (Misc queries) | |||
Shorten a formula | Excel Discussion (Misc queries) | |||
Shorten a Formula | Excel Worksheet Functions | |||
shorten formula | Excel Worksheet Functions | |||
Trying to shorten (or use new) formula | Excel Discussion (Misc queries) |