I'm trying to count then occurences of "C" in the same column on 13
different worksheets (same workbook) named Q1 to Q13. I did a countif
on each sheet, then entered the following formula in a summary sheet:
=sum(Q1:Q13!C65)
I'm getting a #VALUE error because XL keeps inserting single quotes
into my formula so it becomes
=sum(Q1:'Q13'!C65)

Does anyone know why XL is editing my forumla this way? Is there a
workaround?
Thanks.

=SUM('Q1:Q13'!A1)

Put the single quotes in yourself. Without them, I suspect Excel gets
confused thinking this is a cell reference rather than a sheet name. Worked
for me.

--
Regards,
Tom Ogilvy


"davegb" wrote:

> I'm trying to count then occurences of "C" in the same column on 13
> different worksheets (same workbook) named Q1 to Q13. I did a countif
> on each sheet, then entered the following formula in a summary sheet:
> =sum(Q1:Q13!C65)
> I'm getting a #VALUE error because XL keeps inserting single quotes
> into my formula so it becomes
> =sum(Q1:'Q13'!C65)
>
> Does anyone know why XL is editing my forumla this way? Is there a
> workaround?
> Thanks.
>

On Thu, 10 Jul 2008 12:58:13 -0700 (PDT), davegb
<(E-Mail Removed)> wrote:

>I'm trying to count then occurences of "C" in the same column on 13
>different worksheets (same workbook) named Q1 to Q13. I did a countif
>on each sheet, then entered the following formula in a summary sheet:
>=sum(Q1:Q13!C65)
>I'm getting a #VALUE error because XL keeps inserting single quotes
>into my formula so it becomes
>=sum(Q1:'Q13'!C65)
>
>Does anyone know why XL is editing my forumla this way? Is there a
>workaround?
>Thanks.


Try moving the first quote to the beginning, like this:

=sum('Q1:Q3'!C65)

Hope this helps/ Lars-Åke

I think I tested with A1 to verify my suspicions, so to alleviate any
confusion:

=sum('Q1:Q13'!C65)

--
Regards,
Tom Ogilvy


"davegb" wrote:

> I'm trying to count then occurences of "C" in the same column on 13
> different worksheets (same workbook) named Q1 to Q13. I did a countif
> on each sheet, then entered the following formula in a summary sheet:
> =sum(Q1:Q13!C65)
> I'm getting a #VALUE error because XL keeps inserting single quotes
> into my formula so it becomes
> =sum(Q1:'Q13'!C65)
>
> Does anyone know why XL is editing my forumla this way? Is there a
> workaround?
> Thanks.
>

On Jul 10, 2:13*pm, Tom Ogilvy <TomOgi...@discussions.microsoft.com>
wrote:
> I think I tested with A1 to verify my suspicions, so to alleviate any
> confusion:
>
> =sum('Q1:Q13'!C65)
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "davegb" wrote:
> > I'm trying to count then occurences of "C" in the same column on 13
> > different worksheets (same workbook) named Q1 to Q13. I did a countif
> > on each sheet, then entered the following formula in a summary sheet:
> > =sum(Q1:Q13!C65)
> > I'm getting a #VALUE error because XL keeps inserting single quotes
> > into my formula so it becomes
> > =sum(Q1:'Q13'!C65)
>
> > Does anyone know why XL is editing my forumla this way? Is there a
> > workaround?
> > Thanks.- Hide quoted text -
>
> - Show quoted text -

Thanks to all. That did the trick!