Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
Hello,
You use the formula part MATCH(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C $3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) THREE times in your big formula. Move this part into another cell, say Z7 and substitute the 3 instances in your original formula by Z7. If you want to simplify your formula further, get the other parts INDEX(...,MATCH()) out into helper cells, too. With a little cost of additional helper cells you will be rewarded by a huge gain in better overview/simplicity. Regards, Bernd |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
I tried to do this but with no luck. Do i want to input the MATCH(....) part
as a formula or as text? I havent ever done anything using helped cells. thanks Bernd wrote: Hello, You use the formula part MATCH(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C $3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) THREE times in your big formula. Move this part into another cell, say Z7 and substitute the 3 instances in your original formula by Z7. If you want to simplify your formula further, get the other parts INDEX(...,MATCH()) out into helper cells, too. With a little cost of additional helper cells you will be rewarded by a huge gain in better overview/simplicity. Regards, Bernd -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
as a formula, i would imagine.......
=(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C$3:$C $677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) but the actual matching part would be in your other formula. say you put this in Z7, then as Bernd said above, you just use MATCH (Z7,0) in your big formula. susan On Mar 26, 2:59 pm, "brownti via OfficeKB.com" <u31540@uwe wrote: I tried to do this but with no luck. Do i want to input the MATCH(....) part as a formula or as text? I havent ever done anything using helped cells. thanks Bernd wrote: Hello, You use the formula part MATCH(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C $3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) THREE times in your big formula. Move this part into another cell, say Z7 and substitute the 3 instances in your original formula by Z7. If you want to simplify your formula further, get the other parts INDEX(...,MATCH()) out into helper cells, too. With a little cost of additional helper cells you will be rewarded by a huge gain in better overview/simplicity. Regards, Bernd -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200703/1- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
that doesnt seem to work for me. I just get a a #VALUE when i enter =(I36&$U
$17&$J38,'[Master Material Pricing.xls]Master Material'!$C$3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E$677&'[Master Material Pricing. xls]Master Material'!$G$3:$G$677,0) into a cell and then that #VALUE's my other equation. Other ideas? Maybe a better way to write the equation? or a different way of making it work. What i esentially want is something will look up some cells and give me one value when one variable is selected and another value when a different variable is selected. Susan wrote: as a formula, i would imagine....... =(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C$3:$C $677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) but the actual matching part would be in your other formula. say you put this in Z7, then as Bernd said above, you just use MATCH (Z7,0) in your big formula. susan I tried to do this but with no luck. Do i want to input the MATCH(....) part as a formula or as text? I havent ever done anything using helped cells. [quoted text clipped - 25 lines] - Show quoted text - -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200703/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
sorry that doesn't work. i don't know exactly what you're trying to
accomplish with your formulas. perhaps an example will show you what we're trying to tell you (these don't mean anything at all, just examples): cell a1 =25*3 cell b1 =7*4 cell c1 =sum(a1:b1) cell d1 =8 cell e1 =sum(a1+d1) ultimately your final formula, in the cell you want the result in, is: cell g1 =(if(isblank(a1)," ",(d1))*(sum(c1:e1))) whatever - you get the idea. the formula is probably not correct! :) now, imagine that the formula in a1 comes from a different worksheet etc.: cell a1 ='sheet1!'$d$6*'sheet1!'$f$8 cell b1 ='sheet5!'$ab$32*'sheet3!'$ab$33 cell c1 =sum(a1:b1) cell d1 ='sheet5!'$a$8 cell e1 =sum(a1+d1) your formula in cell g1 would STAY THE SAME. it's the little, helper cells that contain the more lengthy, complicated formulas. the helper cells could be put in a hidden column & your main formula could still access them. hope this helps explain it better. :) susan On Mar 26, 3:53 pm, "brownti via OfficeKB.com" <u31540@uwe wrote: that doesnt seem to work for me. I just get a a #VALUE when i enter =(I36&$U $17&$J38,'[Master Material Pricing.xls]Master Material'!$C$3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E$677&'[Master Material Pricing. xls]Master Material'!$G$3:$G$677,0) into a cell and then that #VALUE's my other equation. Other ideas? Maybe a better way to write the equation? or a different way of making it work. What i esentially want is something will look up some cells and give me one value when one variable is selected and another value when a different variable is selected. Susan wrote: as a formula, i would imagine....... =(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C$3:$C $677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) but the actual matching part would be in your other formula. say you put this in Z7, then as Bernd said above, you just use MATCH (Z7,0) in your big formula. susan I tried to do this but with no luck. Do i want to input the MATCH(....) part as a formula or as text? I havent ever done anything using helped cells. [quoted text clipped - 25 lines] - Show quoted text - -- Message posted via OfficeKB.comhttp://www.officekb.com/Uwe/Forums.aspx/excel-programming/200703/1- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
Hello,
Example: Say you have in cell A1: ="I" & " " & "can" & " " & "substitute" & " " & "blanks." Then you can put into B1: =" " [note that you have to start the outsourced part with a "="] and change A1 to: ="I" & B1 & "can" & B1 & "substitute" & B1 & "blanks." Now source the triple MATCH() part out to shorten your formula... Regards, Bernd |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Shorten formula
I'm used to having a single value for the first term of INDEX, and you have a range, so I'm not sure how that works. Is this an array formula? If so, then the following may not apply. I didn't see anybody suggest INDIRECT. MATCH wants the range itself, and not the range address which is text. So put the complex range address in a cell and refer to it with INDIRECT. For example, in cell Z1, put '[Master Material Pricing.xls]Master Material'!$C$3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E$677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677 You may need to format Z1 to text or precede the string with another apostrophe to keep the first apostrophe from being swallowed. Your formula becomes =IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K $677,MATCH(I36&$U$17&$J38,INDIRECT(Z1),0)),IF($A$1 1,INDEX('[Master Material Pricing.xls]Master Material'!$L$3:$L$677,MATCH(I36&$U$17& $J38,INDIRECT(Z1),0)),IF($A$13,INDEX('[Master Material Pricing.xls]Master Material'!$M$3:$M$677,MATCH(I36&$U$17& $J38,INDIRECT(Z1),0)),""))) The logical test term of your IF statements are absolute $A$9 rather than relative A9, and the lookup values of your MATCH statements are mixed I36, $U$17, and $J38, so it is difficult to tell how you cause the formula to repeat. It won't copy down a column very well. Like some of the other comments suggest, use a helper column with the MATCH lookup value. For example, if Z36 has =I36&$U$17&$J38, your formula becomes =IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K $677,MATCH(Z36,INDIRECT(Z1),0)),IF($A$11,INDEX('[Master Material Pricing.xls]Master Material'!$L$3:$L$677,MATCH(Z36,INDIRECT(Z1), 0)),IF($A$13,INDEX('[Master Material Pricing.xls]Master Material'!$M $3:$M$677,MATCH(Z36,INDIRECT(Z1),0)),""))) HTH Carl. On Mar 24, 1:38 am, "Bernd" wrote: Hello, You use the formula part MATCH(I36&$U$17&$J38,'[Master Material Pricing.xls]Master Material'!$C $3:$C$677&'[Master Material Pricing.xls]Master Material'!$E$3:$E $677&'[Master Material Pricing.xls]Master Material'!$G$3:$G$677,0) THREE times in your big formula. Move this part into another cell, say Z7 and substitute the 3 instances in your original formula by Z7. If you want to simplify your formula further, get the other parts INDEX(...,MATCH()) out into helper cells, too. With a little cost of additional helper cells you will be rewarded by a huge gain in better overview/simplicity. Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I shorten an IF formula? | Excel Worksheet Functions | |||
Help to shorten a formula | Excel Worksheet Functions | |||
Shorten a formula | Excel Discussion (Misc queries) | |||
Shorten Formula | Excel Worksheet Functions | |||
Shorten a Formula | Excel Worksheet Functions |