Hi,
I have this formulae:
=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH("
",$D10)),IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Need!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))))

I am not able to activate that formula in array {} format. I should be able
to so that I get the right results. It is part of a macro and macro does not
accept that as an array. I have tried to record that, no acceptance. Problem
seems to be in this part:
IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)

When activating cell content and with shift+ctrl+enter it will be an array
and will give right results.

How could I resolve this problem?
Thanks in advance
MakeLei

On Sun, 26 Aug 2007 22:34:15 -0700, Makelei <(E-Mail Removed)>
wrote:

>Hi,
>I have this formulae:
>=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH("
>",$D10)),IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Need!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))))
>
>I am not able to activate that formula in array {} format. I should be able
>to so that I get the right results. It is part of a macro and macro does not
>accept that as an array. I have tried to record that, no acceptance. Problem
>seems to be in this part:
>IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
>
>When activating cell content and with shift+ctrl+enter it will be an array
>and will give right results.
>
>How could I resolve this problem?
>Thanks in advance
>MakeLei

Take a look at HELP for the FormulaArray Property. Your formula will need to
use the R1C1 reference style.
--ron

Hi Ron,
Thanks for your answer, but:
"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH("
",RC4)),IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF(Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))))"
Does not work either...
Problem seems to be in ths bit:
IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)

Any proposals?

BR
MakeLei


"Ron Rosenfeld" wrote:

> On Sun, 26 Aug 2007 22:34:15 -0700, Makelei <(E-Mail Removed)>
> wrote:
>
> >Hi,
> >I have this formulae:
> >=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH("
> >",$D10)),IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Need!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))))
> >
> >I am not able to activate that formula in array {} format. I should be able
> >to so that I get the right results. It is part of a macro and macro does not
> >accept that as an array. I have tried to record that, no acceptance. Problem
> >seems to be in this part:
> >IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
> >
> >When activating cell content and with shift+ctrl+enter it will be an array
> >and will give right results.
> >
> >How could I resolve this problem?
> >Thanks in advance
> >MakeLei
>
> Take a look at HELP for the FormulaArray Property. Your formula will need to
> use the R1C1 reference style.
> --ron
>

On Mon, 27 Aug 2007 04:18:00 -0700, Makelei <(E-Mail Removed)>
wrote:

>Hi Ron,
>Thanks for your answer, but:
>"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH("
>",RC4)),IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF(Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))))"
>Does not work either...
>Problem seems to be in ths bit:
>IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
>
>Any proposals?
>
>BR
>MakeLei

What does "does not work" mean? It speeds up the trouble shooting process if
you are specific about the problems you are having, rather than just saying
"does not work".

For one thing, your syntax is incorrect.

Take the beginning: "=IF(LEFT(RC4,4)="Ext-",SUM(IF(N ...

If you are including quote marks (") within the quotation, you need to double
them. VB will interpret a single quote as the beginning or end of a string.


"=IF(LEFT(RC4,4)=""Ext-"",SUM(IF(N ...



--ron

http://support.microsoft.com/kb/213181/en-us
Run-time error using FormulaArray property

Your formula is well over 400 characters long.




--
Regards,
Tom Ogilvy


"Makelei" wrote:

> Hi Ron,
> Thanks for your answer, but:
> "=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH("
> ",RC4)),IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF(Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))))"
> Does not work either...
> Problem seems to be in ths bit:
> IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
>
> Any proposals?
>
> BR
> MakeLei
>
>
> "Ron Rosenfeld" wrote:
>
> > On Sun, 26 Aug 2007 22:34:15 -0700, Makelei <(E-Mail Removed)>
> > wrote:
> >
> > >Hi,
> > >I have this formulae:
> > >=IF(LEFT($D10,4)="Ext-",SUM(IF(Need!$F$8:Need!$F$21<>"",IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH("
> > >",$D10)),IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))),SUM(IF(Need!$F$8:Need!$F$21="",IF(Need!$G$8:Need!$G$21=$D10,IF(Need!$E$8:Need!$E$21<>$B$4,IF(Need!$E$8:Need!$E$21<>$B$3,IF(Need!$E$8:Need!$E$21<>$B$2,IF(Need!$E$8:Need!$E$21<>$B$1,Need!T$8:Need!T$21,0),0),0),0)))))
> > >
> > >I am not able to activate that formula in array {} format. I should be able
> > >to so that I get the right results. It is part of a macro and macro does not
> > >accept that as an array. I have tried to record that, no acceptance. Problem
> > >seems to be in this part:
> > >IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
> > >
> > >When activating cell content and with shift+ctrl+enter it will be an array
> > >and will give right results.
> > >
> > >How could I resolve this problem?
> > >Thanks in advance
> > >MakeLei
> >
> > Take a look at HELP for the FormulaArray Property. Your formula will need to
> > use the R1C1 reference style.
> > --ron
> >

On Mon, 27 Aug 2007 04:18:00 -0700, Makelei <(E-Mail Removed)>
wrote:

>Hi Ron,
>Thanks for your answer, but:
>"=IF(LEFT(RC4,4)="Ext-",SUM(IF(Need!R8C6:Need!R21C6<>"",IF(Need!R8C7:Need!R21C7=RIGHT(RC4,LEN(RC4)-SEARCH("
>",RC4)),IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))),SUM(IF(Need!R8C6:Need!R21C6="",IF(Need!R8C7:Need!R21C7=RC4,IF(Need!R8C5:Need!R21C5<>R4C2,IF(Need!R8C5:Need!R21C5<>R3C2,IF(Need!R8C5:Need!R21C5<>R2C2,IF(Need!R8C5:Need!R21C5<>R1C2,Need!R8C[14]:Need!R21C[14],0),0),0),0)))))"
>Does not work either...
>Problem seems to be in ths bit:
>IF(Need!$G$8:Need!$G$21=RIGHT($D10,LEN($D10)-SEARCH(" ",$D10)
>
>Any proposals?
>
>BR
>MakeLei

There is another potential problem and I don't know how to solve it. (Again,
it would have been easier had you defined "does not work").

At least in excel 2003 and earlier, you cannot pass a formula that is longer
than 255 characters. See http://support.microsoft.com/kb/213181/EN-US/
--ron