Shorten formula

[brownti via OfficeKB.com]

I need help shortening a formula. Here is what i have:

=IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K$677,
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)),IF($A$11,INDEX('[Master
Material Pricing.xls]Master Material'!$L$3:$L$677,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)),IF($A$13,INDEX('[Master Material Pricing.xls]Master
Material'!$M$3:$M$677,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)),"")
))

Thats with the master material list open. When it is closed the formula is
too long to display. Is there maybe a macro that could be run when a button
is clicked? this formula repeats many times in the sheet. Thanks.

 

[Guest]

Hi Maybe it is could to explain what you are trying to achieve here! It like
greek to me?

 

[strapping]

I need help shortening a formula. Here is what i have:

=IF($A$9,INDEX('[Master Material Pricing.xls]Master Material'!$K$3:$K$677,
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)),IF($A$11,INDEX('[Master
Material Pricing.xls]Master Material'!$L$3:$L$677,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)),IF($A$13,INDEX('[Master Material Pricing.xls]Master
Material'!$M$3:$M$677,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)),"")
))

Thats with the master material list open. When it is closed the formula is
too long to display. Is there maybe a macro that could be run when a button
is clicked? this formula repeats many times in the sheet. Thanks.

You could start by renaming Master Material Pricing.xls to something
like a.xls, and sheet Master Material to b

 

[Bernd]

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

 

[brownti via OfficeKB.com]

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

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

 

[Susan]

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.
thanks

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

 

[brownti via OfficeKB.com]

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.

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 -

 

[Susan]

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

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.

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 -

 

[Don Guillett]

You have been asked several times to fully describe what you are trying to
do.

 

[Bernd]

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

 

[Carl Hartness]

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$11,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.