HI,
CAN YOU PL FIX THE ERROR - I GET REF! ERROR
=SUMPRODUCT(--(THREED('Jan ''08:May ''08'!$K$6:$K$8)=K12),THREED('Jan
''08:May ''08'!$L$6:$L$8))
this is only for testing before i use on required big range.
thanks in advance

hi all,

My query is on track or out of track and can any one help me.



"Eddy Stan" wrote:

HI,
CAN YOU PL FIX THE ERROR - I GET REF! ERROR
=SUMPRODUCT(--(THREED('Jan ''08:May ''08'!$K$6:$K$8)=K12),THREED('Jan
''08:May ''08'!$L$6:$L$8))
this is only for testing before i use on required big range.
thanks in advance

On Thu, 19 Jun 2008 07:53:04 -0700, Eddy Stan
wrote:

HI,
CAN YOU PL FIX THE ERROR - I GET REF! ERROR
=SUMPRODUCT(--(THREED('Jan ''08:May ''08'!$K$6:$K$8)=K12),THREED('Jan
''08:May ''08'!$L$6:$L$8))
this is only for testing before i use on required big range.
thanks in advance

You should let Excel create the reference instead of typing it in yourself.

In the past, Excel has not always behaved properly when sheet names contain
apostrophe's (single quotes). It seems that THREED has these same limitations.
Can you remove the apostrophe from the sheet names?
--ron

In testing this I've also had problems.

THREED('Jan ''08:May ''08'!$K$6:$K$8)

I don't know what those extra quotes are for but I removed them and tried
this:

=SUMPRODUCT(--(THREED('Jan 08:May 08'!K6:K8)=K12),THREED('Jan 08:May
08'!L6:L8))

This returns a #REF! error.

If I rename the sheets and remove the space it works** :

=SUMPRODUCT(--(THREED(Jan08:May08!K6:K8)=K12),THREED(Jan08:May08 !L6:L8))

** It works until I change data in the range L6:L8 then it returns a #VALUE!
error. If I edit the formula cell (double click then hit enter) it once
again works.

I wanted to post this at the forum but I don't speak French. I've also had
some user interface problems using Morefunc in Excel 2007. If (I) you goto
the Formulas tabMorefunc groupFunctionselect a function, sometimes the
user form doesn't display.

--
Biff
Microsoft Excel MVP


"Ron Rosenfeld" wrote in message
...
On Thu, 19 Jun 2008 07:53:04 -0700, Eddy Stan
wrote:

HI,
CAN YOU PL FIX THE ERROR - I GET REF! ERROR
=SUMPRODUCT(--(THREED('Jan ''08:May ''08'!$K$6:$K$8)=K12),THREED('Jan
''08:May ''08'!$L$6:$L$8))
this is only for testing before i use on required big range.
thanks in advance

You should let Excel create the reference instead of typing it in
yourself.

In the past, Excel has not always behaved properly when sheet names
contain
apostrophe's (single quotes). It seems that THREED has these same
limitations.
Can you remove the apostrophe from the sheet names?
--ron

"T. Valko" wrote...
....
I wanted to post this at the forum but I don't speak French. I've also had
some user interface problems using Morefunc in Excel 2007. If (I) you goto
the Formulas tabMorefunc groupFunctionselect a function, sometimes the
user form doesn't display.
....

You don't really need to speak french to be able to use Longre's
forum, and many of the forum posting are in english. His forum works
the same as the TextPad forum. Use the latter to figure out how to
navigate in the former.

That said, since 3D references can't be dynamic (INDIRECT won't
produce them), the OP would be better off using BLANK bracketing
worksheets named, perhaps, begin and end, with begin immediately
preceeding Jan '08 and end immediately suceeding May '08. Then change
the formulas to something like

=SUMPRODUCT(--(THREED(begin:end!$K$6:$K$8)=K12),THREED(begin:end !$L
$6:$L$8))

which works without problems for me.