ExcelBanter - Shorten formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Shorten formula (https://www.excelbanter.com/excel-programming/385996-re-shorten-formula.html)

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

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

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 -

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

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 -

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

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