HELP ON THREED

  • Thread starter Thread starter Eddy Stan
  • Start date Start date
E

Eddy Stan

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,
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
Click to expand...

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)
Click to expand...

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 tab>Morefunc group>Function>select a function, sometimes the
user form doesn't display.
 
T. Valko said:
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 tab>Morefunc group>Function>select a function, sometimes the
user form doesn't display.
Click to expand...
....

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.
 
Back
Top