#### summarizing worksheet data

```Hi,

I want to create what should be a relatively simple spreadsheet with, say,
fifty worksheets in the workbook. Each worksheet would be identical in terms
of number of rows & columns, as well as the type of data those columns
contain. On the first worksheet, however, I'd like to be able to summarize
the cumulative totals of all the other sheets. So, for instance, if cell a20
on worksheets 2 through 50 contained a formula that calculated a sum of the
figures in a1:a19, I'd like to put a formula in a cell in the first
worksheet that will total the cell a20 in all the other worksheets.

Is there a simple way to do this?

Jim

--
"The trouble with the world is that the stupid are cocksure and the
intelligent are full of doubt." --Bertrand Russell

```
 0
11/3/2006 2:06:47 AM
excel 39879 articles. 2 followers.

13 Replies
528 Views

Similar Articles

[PageSpeed] 17

```Jim wrote:
> Hi,
>
> I want to create what should be a relatively simple spreadsheet with, say,
> fifty worksheets in the workbook. Each worksheet would be identical in terms
> of number of rows & columns, as well as the type of data those columns
> contain. On the first worksheet, however, I'd like to be able to summarize
> the cumulative totals of all the other sheets. So, for instance, if cell a20
> on worksheets 2 through 50 contained a formula that calculated a sum of the
> figures in a1:a19, I'd like to put a formula in a cell in the first
> worksheet that will total the cell a20 in all the other worksheets.
>
> Is there a simple way to do this?
>
>
> Jim
>
> --
> "The trouble with the world is that the stupid are cocksure and the
> intelligent are full of doubt." --Bertrand Russell

Hi Jim,

See Excel Help "Refer to the same cell or range on multiple sheets"

Ken Johnson

```
 0
KenCJohnson (314)
11/3/2006 6:57:13 AM
```=SUM(Sheet2:Sheet50!A20)

Jim wrote:

> Hi,
>
> I want to create what should be a relatively simple spreadsheet with, say,
> fifty worksheets in the workbook. Each worksheet would be identical in terms
> of number of rows & columns, as well as the type of data those columns
> contain. On the first worksheet, however, I'd like to be able to summarize
> the cumulative totals of all the other sheets. So, for instance, if cell a20
> on worksheets 2 through 50 contained a formula that calculated a sum of the
> figures in a1:a19, I'd like to put a formula in a cell in the first
> worksheet that will total the cell a20 in all the other worksheets.
>
> Is there a simple way to do this?
>
>
> Jim
>
> --
> "The trouble with the world is that the stupid are cocksure and the
> intelligent are full of doubt." --Bertrand Russell

```
 0
11/3/2006 1:16:07 PM
```There is a shortcut for entering this:

=SUM('*'!A20)

where the '*' automatically converts to all sheets other than the
activate one. Excel 2002 seems to have introduced a bug where this can
crash the application. Does anyone else have the same problem?

Lori wrote:

> =SUM(Sheet2:Sheet50!A20)
>
> Jim wrote:
>
> > Hi,
> >
> > I want to create what should be a relatively simple spreadsheet with, say,
> > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > of number of rows & columns, as well as the type of data those columns
> > contain. On the first worksheet, however, I'd like to be able to summarize
> > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > figures in a1:a19, I'd like to put a formula in a cell in the first
> > worksheet that will total the cell a20 in all the other worksheets.
> >
> > Is there a simple way to do this?
> >
> >
> > Jim
> >
> > --
> > "The trouble with the world is that the stupid are cocksure and the
> > intelligent are full of doubt." --Bertrand Russell

```
 0
11/3/2006 1:22:31 PM
```It worked ok in xl2003 for my simple test.

(I had never seen this before!)

Lori wrote:
>
> There is a shortcut for entering this:
>
> =SUM('*'!A20)
>
> where the '*' automatically converts to all sheets other than the
> activate one. Excel 2002 seems to have introduced a bug where this can
> crash the application. Does anyone else have the same problem?
>
> Lori wrote:
>
> > =SUM(Sheet2:Sheet50!A20)
> >
> > Jim wrote:
> >
> > > Hi,
> > >
> > > I want to create what should be a relatively simple spreadsheet with, say,
> > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > of number of rows & columns, as well as the type of data those columns
> > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > worksheet that will total the cell a20 in all the other worksheets.
> > >
> > > Is there a simple way to do this?
> > >
> > > Thanks in advance,
> > >
> > > Jim
> > >
> > > --
> > > "The trouble with the world is that the stupid are cocksure and the
> > > intelligent are full of doubt." --Bertrand Russell

--

Dave Peterson
```
 0
petersod (12004)
11/3/2006 2:18:04 PM
```I have found it can crash on 2002 if I first omit the single quotes and

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

Dave Peterson wrote:

> It worked ok in xl2003 for my simple test.
>
> (I had never seen this before!)
>
> Lori wrote:
> >
> > There is a shortcut for entering this:
> >
> > =SUM('*'!A20)
> >
> > where the '*' automatically converts to all sheets other than the
> > activate one. Excel 2002 seems to have introduced a bug where this can
> > crash the application. Does anyone else have the same problem?
> >
> > Lori wrote:
> >
> > > =SUM(Sheet2:Sheet50!A20)
> > >
> > > Jim wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to create what should be a relatively simple spreadsheet with, say,
> > > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > > of number of rows & columns, as well as the type of data those columns
> > > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > worksheet that will total the cell a20 in all the other worksheets.
> > > >
> > > > Is there a simple way to do this?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Jim
> > > >
> > > > --
> > > > "The trouble with the world is that the stupid are cocksure and the
> > > > intelligent are full of doubt." --Bertrand Russell
>
> --
>
> Dave Peterson

```
 0
11/3/2006 4:20:56 PM
```I have found it can crash on 2002 if I first omit the single quotes and

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

Dave Peterson wrote:

> It worked ok in xl2003 for my simple test.
>
> (I had never seen this before!)
>
> Lori wrote:
> >
> > There is a shortcut for entering this:
> >
> > =SUM('*'!A20)
> >
> > where the '*' automatically converts to all sheets other than the
> > activate one. Excel 2002 seems to have introduced a bug where this can
> > crash the application. Does anyone else have the same problem?
> >
> > Lori wrote:
> >
> > > =SUM(Sheet2:Sheet50!A20)
> > >
> > > Jim wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to create what should be a relatively simple spreadsheet with, say,
> > > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > > of number of rows & columns, as well as the type of data those columns
> > > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > worksheet that will total the cell a20 in all the other worksheets.
> > > >
> > > > Is there a simple way to do this?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Jim
> > > >
> > > > --
> > > > "The trouble with the world is that the stupid are cocksure and the
> > > > intelligent are full of doubt." --Bertrand Russell
>
> --
>
> Dave Peterson

```
 0
11/3/2006 4:21:46 PM
```You're right Lori.

I crashed my XL02 by being a little lazy.

Copied a working formula to another cell:

=SUM(Sheet2:Sheet5!F1)

Selected <<Sheet2:Sheet5>> in the formula bar,
And typed << '*' >>

As I typed the last single quote, XL crashed!
I *didn't* even have time to hit <Enter> before it happened ! ! !

After recovering the WB, entering the formula from scratch worked perfectly
fine.

When Windows asked for the error report to be sent, I OK'd it.
Got a message back that a fix existed.
Was told to update Office.
I update Windows religiously, can't remember when I last updated Office.

After the Office update, tried the same formula revision again, and XL
*STILL* crashed.
Sending the error report this time generated *no* "able to fix" message.

Haven't tested if revising the '*ABC*' type argument has the same (crash)
results.

Have you?
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

"Lori" <lorimer_miller@hotmail.com> wrote in message
I have found it can crash on 2002 if I first omit the single quotes and

I came across it by accident and haven't seen it documented - think it
probably goes back to XL5 days and used to work fine in 97. It can be
useful for referencing specific sheets in large workbooks e.g. '*abc*'!
evaluates to all sheets containing "abc".

Dave Peterson wrote:

> It worked ok in xl2003 for my simple test.
>
> (I had never seen this before!)
>
> Lori wrote:
> >
> > There is a shortcut for entering this:
> >
> > =SUM('*'!A20)
> >
> > where the '*' automatically converts to all sheets other than the
> > activate one. Excel 2002 seems to have introduced a bug where this can
> > crash the application. Does anyone else have the same problem?
> >
> > Lori wrote:
> >
> > > =SUM(Sheet2:Sheet50!A20)
> > >
> > > Jim wrote:
> > >
> > > > Hi,
> > > >
> > > > I want to create what should be a relatively simple spreadsheet
> > > > with, say,
> > > > fifty worksheets in the workbook. Each worksheet would be identical
> > > > in terms
> > > > of number of rows & columns, as well as the type of data those
> > > > columns
> > > > contain. On the first worksheet, however, I'd like to be able to
> > > > summarize
> > > > the cumulative totals of all the other sheets. So, for instance, if
> > > > cell a20
> > > > on worksheets 2 through 50 contained a formula that calculated a sum
> > > > of the
> > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > worksheet that will total the cell a20 in all the other worksheets.
> > > >
> > > > Is there a simple way to do this?
> > > >
> > > > Thanks in advance,
> > > >
> > > > Jim
> > > >
> > > > --
> > > > "The trouble with the world is that the stupid are cocksure and the
> > > > intelligent are full of doubt." --Bertrand Russell
>
> --
>
> Dave Peterson

```
 0
ragdyer1 (4060)
11/3/2006 5:00:20 PM
```Hey, that one is neat, too.

(Not the crashing part <bg>)

Lori wrote:
>
> I have found it can crash on 2002 if I first omit the single quotes and
>
> I came across it by accident and haven't seen it documented - think it
> probably goes back to XL5 days and used to work fine in 97. It can be
> useful for referencing specific sheets in large workbooks e.g. '*abc*'!
> evaluates to all sheets containing "abc".
>
> Dave Peterson wrote:
>
> > It worked ok in xl2003 for my simple test.
> >
> > (I had never seen this before!)
> >
> > Lori wrote:
> > >
> > > There is a shortcut for entering this:
> > >
> > > =SUM('*'!A20)
> > >
> > > where the '*' automatically converts to all sheets other than the
> > > activate one. Excel 2002 seems to have introduced a bug where this can
> > > crash the application. Does anyone else have the same problem?
> > >
> > > Lori wrote:
> > >
> > > > =SUM(Sheet2:Sheet50!A20)
> > > >
> > > > Jim wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > I want to create what should be a relatively simple spreadsheet with, say,
> > > > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > > > of number of rows & columns, as well as the type of data those columns
> > > > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > > worksheet that will total the cell a20 in all the other worksheets.
> > > > >
> > > > > Is there a simple way to do this?
> > > > >
> > > > > Thanks in advance,
> > > > >
> > > > > Jim
> > > > >
> > > > > --
> > > > > "The trouble with the world is that the stupid are cocksure and the
> > > > > intelligent are full of doubt." --Bertrand Russell
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12004)
11/3/2006 6:58:40 PM
```So it can crash in 03 and probably 07 too? :(  To be safe you could
enter the formula as text (with leading apostrophe) and then evaluate
using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
defined name works  - though stability is obviously questionable. :)

Dave Peterson wrote:
> Hey, that one is neat, too.
>
> (Not the crashing part <bg>)
>
> Lori wrote:
> >
> > I have found it can crash on 2002 if I first omit the single quotes and
> >
> > I came across it by accident and haven't seen it documented - think it
> > probably goes back to XL5 days and used to work fine in 97. It can be
> > useful for referencing specific sheets in large workbooks e.g. '*abc*'!
> > evaluates to all sheets containing "abc".
> >
> > Dave Peterson wrote:
> >
> > > It worked ok in xl2003 for my simple test.
> > >
> > > (I had never seen this before!)
> > >
> > > Lori wrote:
> > > >
> > > > There is a shortcut for entering this:
> > > >
> > > > =SUM('*'!A20)
> > > >
> > > > where the '*' automatically converts to all sheets other than the
> > > > activate one. Excel 2002 seems to have introduced a bug where this can
> > > > crash the application. Does anyone else have the same problem?
> > > >
> > > > Lori wrote:
> > > >
> > > > > =SUM(Sheet2:Sheet50!A20)
> > > > >
> > > > > Jim wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > I want to create what should be a relatively simple spreadsheet with, say,
> > > > > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > > > > of number of rows & columns, as well as the type of data those columns
> > > > > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > > > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > > > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > > > worksheet that will total the cell a20 in all the other worksheets.
> > > > > >
> > > > > > Is there a simple way to do this?
> > > > > >
> > > > > > Thanks in advance,
> > > > > >
> > > > > > Jim
> > > > > >
> > > > > > --
> > > > > > "The trouble with the world is that the stupid are cocksure and the
> > > > > > intelligent are full of doubt." --Bertrand Russell
> > >
> > > --
> > >
> > > Dave Peterson
>
> --
>
> Dave Peterson

```
 0
11/3/2006 8:50:23 PM
```It did not crash for me in xl2003.  (Sorry about the confusion.)

Lori wrote:
>
> So it can crash in 03 and probably 07 too? :(  To be safe you could
> enter the formula as text (with leading apostrophe) and then evaluate
> using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
> defined name works  - though stability is obviously questionable. :)
>
> Dave Peterson wrote:
> > Hey, that one is neat, too.
> >
> > (Not the crashing part <bg>)
> >
> > Lori wrote:
> > >
> > > I have found it can crash on 2002 if I first omit the single quotes and
> > >
> > > I came across it by accident and haven't seen it documented - think it
> > > probably goes back to XL5 days and used to work fine in 97. It can be
> > > useful for referencing specific sheets in large workbooks e.g. '*abc*'!
> > > evaluates to all sheets containing "abc".
> > >
> > > Dave Peterson wrote:
> > >
> > > > It worked ok in xl2003 for my simple test.
> > > >
> > > > (I had never seen this before!)
> > > >
> > > > Lori wrote:
> > > > >
> > > > > There is a shortcut for entering this:
> > > > >
> > > > > =SUM('*'!A20)
> > > > >
> > > > > where the '*' automatically converts to all sheets other than the
> > > > > activate one. Excel 2002 seems to have introduced a bug where this can
> > > > > crash the application. Does anyone else have the same problem?
> > > > >
> > > > > Lori wrote:
> > > > >
> > > > > > =SUM(Sheet2:Sheet50!A20)
> > > > > >
> > > > > > Jim wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > I want to create what should be a relatively simple spreadsheet with, say,
> > > > > > > fifty worksheets in the workbook. Each worksheet would be identical in terms
> > > > > > > of number of rows & columns, as well as the type of data those columns
> > > > > > > contain. On the first worksheet, however, I'd like to be able to summarize
> > > > > > > the cumulative totals of all the other sheets. So, for instance, if cell a20
> > > > > > > on worksheets 2 through 50 contained a formula that calculated a sum of the
> > > > > > > figures in a1:a19, I'd like to put a formula in a cell in the first
> > > > > > > worksheet that will total the cell a20 in all the other worksheets.
> > > > > > >
> > > > > > > Is there a simple way to do this?
> > > > > > >
> > > > > > > Thanks in advance,
> > > > > > >
> > > > > > > Jim
> > > > > > >
> > > > > > > --
> > > > > > > "The trouble with the world is that the stupid are cocksure and the
> > > > > > > intelligent are full of doubt." --Bertrand Russell
> > > >
> > > > --
> > > >
> > > > Dave Peterson
> >
> > --
> >
> > Dave Peterson

--

Dave Peterson
```
 0
petersod (12004)
11/3/2006 8:59:06 PM
```Works fine in XL2k!

So it's just the one on the middle - XL02.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lori" <lorimer_miller@hotmail.com> wrote in message
> So it can crash in 03 and probably 07 too? :(  To be safe you could
> enter the formula as text (with leading apostrophe) and then evaluate
> using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
> defined name works  - though stability is obviously questionable. :)
>
> Dave Peterson wrote:
>> Hey, that one is neat, too.
>>
>> (Not the crashing part <bg>)
>>
>> Lori wrote:
>> >
>> > I have found it can crash on 2002 if I first omit the single quotes and
>> >
>> > I came across it by accident and haven't seen it documented - think it
>> > probably goes back to XL5 days and used to work fine in 97. It can be
>> > useful for referencing specific sheets in large workbooks e.g. '*abc*'!
>> > evaluates to all sheets containing "abc".
>> >
>> > Dave Peterson wrote:
>> >
>> > > It worked ok in xl2003 for my simple test.
>> > >
>> > > (I had never seen this before!)
>> > >
>> > > Lori wrote:
>> > > >
>> > > > There is a shortcut for entering this:
>> > > >
>> > > > =SUM('*'!A20)
>> > > >
>> > > > where the '*' automatically converts to all sheets other than the
>> > > > activate one. Excel 2002 seems to have introduced a bug where this
>> > > > can
>> > > > crash the application. Does anyone else have the same problem?
>> > > >
>> > > > Lori wrote:
>> > > >
>> > > > > =SUM(Sheet2:Sheet50!A20)
>> > > > >
>> > > > > Jim wrote:
>> > > > >
>> > > > > > Hi,
>> > > > > >
>> > > > > > I want to create what should be a relatively simple spreadsheet
>> > > > > > with, say,
>> > > > > > fifty worksheets in the workbook. Each worksheet would be
>> > > > > > identical in terms
>> > > > > > of number of rows & columns, as well as the type of data those
>> > > > > > columns
>> > > > > > contain. On the first worksheet, however, I'd like to be able
>> > > > > > to summarize
>> > > > > > the cumulative totals of all the other sheets. So, for
>> > > > > > instance, if cell a20
>> > > > > > on worksheets 2 through 50 contained a formula that calculated
>> > > > > > a sum of the
>> > > > > > figures in a1:a19, I'd like to put a formula in a cell in the
>> > > > > > first
>> > > > > > worksheet that will total the cell a20 in all the other
>> > > > > > worksheets.
>> > > > > >
>> > > > > > Is there a simple way to do this?
>> > > > > >
>> > > > > > Thanks in advance,
>> > > > > >
>> > > > > > Jim
>> > > > > >
>> > > > > > --
>> > > > > > "The trouble with the world is that the stupid are cocksure and
>> > > > > > the
>> > > > > > intelligent are full of doubt." --Bertrand Russell
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>>
>> --
>>
>> Dave Peterson
>

```
 0
ragdyer1 (4060)
11/4/2006 1:54:14 AM
```That's good to hear. So there's only one caveat then - be very careful
when using with XL2002!

One further point: aside from stability, evaluating these expressions
without using the formula bar can also add flexibility.

e.g. Suppose you want to sum cells in all sheets beginning with
a,b,c,... You could enter a,b,c,... down column A of the master sheet
and in column B fill down formulas

="=sum('"&A1&"*'a1)"

which could be evaluated by pasting values and then choosing Edit >
Replace "=" with "=".
Or to count all sheets with nonempty cells in a script you could use:

?evaluate("counta('[Book1]*'!a1)")

Anyway we've drifted off Jim's original innocuous question far enough
and I've run out of new material. Glad it lead to a fruitful discussion
though.

RagDyer wrote:
> Works fine in XL2k!
>
> So it's just the one on the middle - XL02.
> --
> Regards,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------
> "Lori" <lorimer_miller@hotmail.com> wrote in message
> > So it can crash in 03 and probably 07 too? :(  To be safe you could
> > enter the formula as text (with leading apostrophe) and then evaluate
> > using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
> > defined name works  - though stability is obviously questionable. :)
> >
> > Dave Peterson wrote:
> >> Hey, that one is neat, too.
> >>
> >> (Not the crashing part <bg>)
> >>
> >> Lori wrote:
> >> >
> >> > I have found it can crash on 2002 if I first omit the single quotes and
> >> >
> >> > I came across it by accident and haven't seen it documented - think it
> >> > probably goes back to XL5 days and used to work fine in 97. It can be
> >> > useful for referencing specific sheets in large workbooks e.g. '*abc*'!
> >> > evaluates to all sheets containing "abc".
> >> >
> >> > Dave Peterson wrote:
> >> >
> >> > > It worked ok in xl2003 for my simple test.
> >> > >
> >> > > (I had never seen this before!)
> >> > >
> >> > > Lori wrote:
> >> > > >
> >> > > > There is a shortcut for entering this:
> >> > > >
> >> > > > =SUM('*'!A20)
> >> > > >
> >> > > > where the '*' automatically converts to all sheets other than the
> >> > > > activate one. Excel 2002 seems to have introduced a bug where this
> >> > > > can
> >> > > > crash the application. Does anyone else have the same problem?
> >> > > >
> >> > > > Lori wrote:
> >> > > >
> >> > > > > =SUM(Sheet2:Sheet50!A20)
> >> > > > >
> >> > > > > Jim wrote:
> >> > > > >
> >> > > > > > Hi,
> >> > > > > >
> >> > > > > > I want to create what should be a relatively simple spreadsheet
> >> > > > > > with, say,
> >> > > > > > fifty worksheets in the workbook. Each worksheet would be
> >> > > > > > identical in terms
> >> > > > > > of number of rows & columns, as well as the type of data those
> >> > > > > > columns
> >> > > > > > contain. On the first worksheet, however, I'd like to be able
> >> > > > > > to summarize
> >> > > > > > the cumulative totals of all the other sheets. So, for
> >> > > > > > instance, if cell a20
> >> > > > > > on worksheets 2 through 50 contained a formula that calculated
> >> > > > > > a sum of the
> >> > > > > > figures in a1:a19, I'd like to put a formula in a cell in the
> >> > > > > > first
> >> > > > > > worksheet that will total the cell a20 in all the other
> >> > > > > > worksheets.
> >> > > > > >
> >> > > > > > Is there a simple way to do this?
> >> > > > > >
> >> > > > > > Thanks in advance,
> >> > > > > >
> >> > > > > > Jim
> >> > > > > >
> >> > > > > > --
> >> > > > > > "The trouble with the world is that the stupid are cocksure and
> >> > > > > > the
> >> > > > > > intelligent are full of doubt." --Bertrand Russell
> >> > >
> >> > > --
> >> > >
> >> > > Dave Peterson
> >>
> >> --
> >>
> >> Dave Peterson
> >

```
 0
11/4/2006 10:44:09 AM
```thanks to all for the input - it was very helpful.

Jim

"Lori" <lorimer_miller@hotmail.com> wrote in message
> That's good to hear. So there's only one caveat then - be very careful
> when using with XL2002!
>
> One further point: aside from stability, evaluating these expressions
> without using the formula bar can also add flexibility.
>
> e.g. Suppose you want to sum cells in all sheets beginning with
> a,b,c,... You could enter a,b,c,... down column A of the master sheet
> and in column B fill down formulas
>
> ="=sum('"&A1&"*'a1)"
>
> which could be evaluated by pasting values and then choosing Edit >
> Replace "=" with "=".
> Or to count all sheets with nonempty cells in a script you could use:
>
> ?evaluate("counta('[Book1]*'!a1)")
>
> Anyway we've drifted off Jim's original innocuous question far enough
> and I've run out of new material. Glad it lead to a fruitful discussion
> though.
>
>
> RagDyer wrote:
> > Works fine in XL2k!
> >
> > So it's just the one on the middle - XL02.
> > --
> > Regards,
> >
> > RD
> >
>
> --------------------------------------------------------------------------
-
> > Please keep all correspondence within the NewsGroup, so all may benefit
!
>
> --------------------------------------------------------------------------
-
> > "Lori" <lorimer_miller@hotmail.com> wrote in message
> > > So it can crash in 03 and probably 07 too? :(  To be safe you could
> > > enter the formula as text (with leading apostrophe) and then evaluate
> > > using Text to Columns > Finish. Also =EVALUATE("sum('*'!A1)") as a
> > > defined name works  - though stability is obviously questionable. :)
> > >
> > > Dave Peterson wrote:
> > >> Hey, that one is neat, too.
> > >>
> > >> (Not the crashing part <bg>)
> > >>
> > >> Lori wrote:
> > >> >
> > >> > I have found it can crash on 2002 if I first omit the single quotes
and
> > >> >
> > >> > I came across it by accident and haven't seen it documented - think
it
> > >> > probably goes back to XL5 days and used to work fine in 97. It can
be
> > >> > useful for referencing specific sheets in large workbooks e.g.
'*abc*'!
> > >> > evaluates to all sheets containing "abc".
> > >> >
> > >> > Dave Peterson wrote:
> > >> >
> > >> > > It worked ok in xl2003 for my simple test.
> > >> > >
> > >> > > (I had never seen this before!)
> > >> > >
> > >> > > Lori wrote:
> > >> > > >
> > >> > > > There is a shortcut for entering this:
> > >> > > >
> > >> > > > =SUM('*'!A20)
> > >> > > >
> > >> > > > where the '*' automatically converts to all sheets other than
the
> > >> > > > activate one. Excel 2002 seems to have introduced a bug where
this
> > >> > > > can
> > >> > > > crash the application. Does anyone else have the same problem?
> > >> > > >
> > >> > > > Lori wrote:
> > >> > > >
> > >> > > > > =SUM(Sheet2:Sheet50!A20)
> > >> > > > >
> > >> > > > > Jim wrote:
> > >> > > > >
> > >> > > > > > Hi,
> > >> > > > > >
> > >> > > > > > I want to create what should be a relatively simple
> > >> > > > > > with, say,
> > >> > > > > > fifty worksheets in the workbook. Each worksheet would be
> > >> > > > > > identical in terms
> > >> > > > > > of number of rows & columns, as well as the type of data
those
> > >> > > > > > columns
> > >> > > > > > contain. On the first worksheet, however, I'd like to be
able
> > >> > > > > > to summarize
> > >> > > > > > the cumulative totals of all the other sheets. So, for
> > >> > > > > > instance, if cell a20
> > >> > > > > > on worksheets 2 through 50 contained a formula that
calculated
> > >> > > > > > a sum of the
> > >> > > > > > figures in a1:a19, I'd like to put a formula in a cell in
the
> > >> > > > > > first
> > >> > > > > > worksheet that will total the cell a20 in all the other
> > >> > > > > > worksheets.
> > >> > > > > >
> > >> > > > > > Is there a simple way to do this?
> > >> > > > > >
> > >> > > > > > Thanks in advance,
> > >> > > > > >
> > >> > > > > > Jim
> > >> > > > > >
> > >> > > > > > --
> > >> > > > > > "The trouble with the world is that the stupid are cocksure
and
> > >> > > > > > the
> > >> > > > > > intelligent are full of doubt." --Bertrand Russell
> > >> > >
> > >> > > --
> > >> > >
> > >> > > Dave Peterson
> > >>
> > >> --
> > >>
> > >> Dave Peterson
> > >
>

```
 0
11/6/2006 8:26:15 PM

Similar Artilces:

compare 2 columns, enter data from other column
I have been struggling with a formula - help! I want to copy data from one sheet to another but only if there is an exact match of data. I need help with the structure of the formula - I can massage it later to fit the specifics. First, I need to find any exact match of the text in D1 of Doc1 to any row in column E of Doc2. If there is a match, then: from that match's row in Doc2, I want to enter the data from column A into column B of L1's row in Doc1. If there is no match, nothing happens. Can anyone help? Spent hours trying to set this up. I need the fo...

Pre-formatting Cells to look blank until data is entered
Using Microsoft Excel 2003 Is it possible to pre-set formatting on cells so that the formatting on those cells stay blank until data is entered, then the pre-set formatting is autmatically applied? For Example: Instead of having the entire sheet with Border Formatting, and having pages with blank data w/formatting, the pages will look blank until data is entered, then the formatting will be applied. Does that make sense? Troy Troy, For example, you could use an event: this will copy the format of the cell above after the entry has been made, for any single cell entry in columns A or B...

Get data from Access file
HI All, Is it possible to populate a cell from a cell (if that=92s what it=92s called) in an Access file? The access file is called qcpProg.mdb in a directory level one above the excel file. The data is in the tProduct table, UnitCost field (currency), with the Product Code field (text) that is equal to Joseph4. It would save me a lot of manual updates if I could do this. Thanks! Michele Hi Michele, In excel 2000, I use Data> Get External Data> New Database query. Would that work in your case? Dan Try this. You'll need to add a reference to the AD...

How to convert data/time to string in query?
How to convert date/time to string in query? So I can handle it with using "Left" or "Right" function. Thanks for any help in advance! You can do that with the Format function. However, if your purpose is to extract a part of the date, you can do that in a more locale-independent way using the various functions designed for that purpose, such as Year(), Month(), Day(), etc. Here's an example that on my system (where short date format includes four-digit years) returns the same value in both columns (albeit one is a string and the other is an integer). The first...

ODBC data source Table error
Sorry for the duplicate post but I am desperate and am trying a more active forum. Original post: I have a Visio drawing with one shape that is linked to an Excel spreadsheet. If I right click and select "Refresh Shape Properties" it will refresh correctly. However, if I deselect the shape and right click on the drawing and select "Refresh Linked Shapes" I get an error response "the table "VisioTransfer" specified in the selected shape does not exist in the ODBC data source. The only data source file assigned does have the table "VisioTransfer" ...

Enter data on one sheet and copy to others
I'm not sure if this can be done or not...i currently have a workbook with 3 sheets, sheet one is the master sheet and i have some vba coding that if column a is not blank it copies over to sheet 2, this is working well but now i have added sheet 3 and need the master to copy to it, it's a little more complicated because some of the cells already contains information and what i need is for it to go to the next available cell within that row, basically fill in the blanks; column b in the master needs to copy to column a in sheet 3, d and f in the master needs to concatenat...

data markers in graphs overlap
When I make a point graph in excel, some of my data points are positioned exactly on top of each other. That's a problem, because my winter data has the same value as my summer data. But now I can not see whether my summer data is hidden behind my winter data or that maybe my summer data is altogether flawed and not in my graph at all. Of course I can use different markers for both data types, but I'd rather use an offset option or something like that. I use Excel 2003 If it's not going to upset the display of values, you can jitter the data, that is, add or subtract a sli...

Worksheets open behind tool bars
Using Office MacX Excel opening worksheets results in the top being behind the tool bars. I have to drag the bottom corner of the worksheet far to the right and up. Then I can reach the top bar of the workbook and drag it below the tool bars and formula bar. This is a real PAIN. Any suggestions? ...

How to selectively insert data in fields
Hi all, I have a table "tblBills" with fields "orderID", "supplier", "suppliername" and another table "tblInstSales" with fields "orderID", "installername", "salesrepname" How can I update the field "suppliername" based on what exists in field "supplier" Example: if in field "supplier" entry is "Installer" then use data from field "installername" and if it is "Salesrep" then use data from "salesrepname" I am a newbie with limited query ...

Chart data series background color
How do I change the background color for the data series? I want a bar chart to display the bars with a blue color. When a number in the data series is negative, I want the bar representing the negative number to be red. On the Format Data Series window on the Patterns tab, I have checked the box that says "Invert if negative." Now when negative, the bar color is white. I can't find where to set the background color to red. You can select a different background colour to format the column. There will be a pattern showing in the column, but this may be an acceptable opti...

Conditional Formatting
Situation: Let's say you have a list in Excel. Within that list, there is a column to which you have applied conditional formatting. However, for some reason, you have applied 2 different types of conditional formatting in the same column. Perhaps the list contains 2 different classes of employees, and you want to distinguish achievements with conditional formatting that not only sets standards higher for one group, but makes the formatting different as well. At some point, you might want to sort that list to see who is doing well. However, when you sort, the formatting remains ...

worksheets slow to be recognized by client
recently i have been having problems with some of the store clients not seeing a worksheet just after its created. sometimes I have to wait as long as 6 or 8 minutes for the client to see and run the worksheet. any thoughts ? it has been running fine for years but just recently started seeing this on several of my stores. This is a multi-part message in MIME format. ------=_NextPart_000_014E_01CA5BCD.317847B0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Conradical, How often are the stores connecting to HQ? They won...

Worksheet Tabs Missing!
It's an intermittent problem. Is there a way to bring back the sheet tabs (at the bottom) when they vanish? Did I inadvertently change a setting? Thanks! -- John Likely you did inadvertently change a setting. The setting is "per workbook"; meaning if you change it, the setting stays with the workbook. Just go to Tools-->Options, View tab, and check Worksheet tabs. ************ Anne Troy www.OfficeArticles.com "Training Spec." <TrainingSpec@discussions.microsoft.com> wrote in message news:33AA19C9-DB0C-4E1B-8201-A1DF400BD08B@microsoft.com... > It'...

CollegestudentHelp me create an automated data entry program in Ex #2
I have detailed customer information and I want to be able to type in the customer ID and have the rest of the information fill itself in. Please help. All in Excell. Hi, Where is your detailed information of customer? Is it in another excel sheet or some where else? If it is in another excel sheet you can use "vlookup" formula. Regards akhileshdalia<at>hotmail<dot>com "College Student" wrote: > I have detailed customer information and I want to be able to type in the > customer ID and have the rest of the information fill itself in. Please &g...

Summarizing changes to a memo field
Firstly, sorry for posting this here, but I need to do this in Access and I can't see any other place to post the question and I know it's going to involve lots of VBA... Now to give a brief description of where this is coming from. My office is a 24/7 operation which answers for a variety of different offices and locations. We use an Access database to store our procedures and help notes. Primarily the entries consist of a Subject line and a textbox whose Controlsource is a Memo field. The textbox is in a monospace font to allow for easy formatting to make the procedures legible with...

Move data from Primary table to a One to Many Table
I have a DB which has contact info stored in a One-to-Many table. The main Table has some additional contact info which I would like stored in the One-to-Many table. Data is seperated by fields such as Name, HomePhone and WorkPhone. These fields exist in the One-to-Many table as well. Is there a way to run a Query that would accomplish this? If so how? Many thanks in advance. "Mary M" <none@no_email.com> wrote in message news:eD76N4ykHHA.4876@TK2MSFTNGP03.phx.gbl... >I have a DB which has contact info stored in a One-to-Many table. The main >Table has some addi...

Limiting Viewing of a WorkSheet
I have a spreadsheet that I have a data worksheet that I need to someho password protect it so no one can view it as it contains employee dat (salary,etc). This data is used to calulate several other things o another worksheet. Any way to password protect the worksheet so no on can view it -- toy4x ----------------------------------------------------------------------- toy4x4's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1459 View this thread: http://www.excelforum.com/showthread.php?threadid=26221 Some ol' code from MVP Jan Karel Pieterse: http://tinyu...

Create a template for every worksheet
I need to create a template that can be updated later for 80-some worksheets in a workbook. Basically, each tab has some information in common (which is updated every year) but some information which is unique. In the past, I just created the first worksheet without any unique information, and copied/pasted to new worksheets. The problem is, if I need to update something on all of them, I have to go back to everyone individually and copy/paste. Is there a way to make a template that will automatically update all worksheets based on it? Thanks! ...

updating a worksheet from another worksheet
I have a workbook that contains worksheets for each Month and 1 worksheet called Combined Example : Worksheet names in workbook are: January February March Combined Each of the worksheets except the Combined worksheet contains the same column Titles Example January worksheet has columns A1 = NAME B1 = Telephone c1 = City D1 = State February worksheet has columns A1 = NAME B1 = Telephone c1 = City D1 = State March worksheet has columns A1 = NAME B1 = Telephone c1 = City D1 = State Combined worksheet has A1 = City B1 = Name What I am looking to do to the Combin...

Purchase Request Resolution Report contains no data
Our user would like to print all the items listed in Purchase Request Resolution Grid but when we click on the Print button the report doesn't bring up any data. Any idea? Speedy, The report attached to the print button on Purchasing Request Resolution contains the "Actions" that were taken on items in the window. For example. Action Performed PO Number Vendor ID Vendor Name Vendor Item Number Item Number Item Description Quantity Ordered U of M Site ID Due Date =================================================...

deleting worksheet #3
Hello, i would like to know how i can delete a worksheet automaticaly without confirming it in the dialog box. Thanks. You need VBA, and to set displayalerts off Application.DisplayAlerts = False Worksheets("Sheet1").Delete APplication.DisplayAlerts = True -- HTH RP (remove nothere from the email address if mailing direct) "Laurent M" <anonymous@discussions.microsoft.com> wrote in message news:057e01c502cf\$0ce7f0a0\$a601280a@phx.gbl... > Hello, > > i would like to know how i can delete a worksheet > automaticaly without confirming it i...

Converting Vertical Data into Horizontal Data
I have a list of 6 performance ratings for each employee. The ratings are listed vertically. How do I convert the ratings into one horizontal row for each employee? See your other posting. -- Russell Dawson Excel Student Please hit "Yes" if this post was helpful. "Kiley" wrote: > I have a list of 6 performance ratings for each employee. The ratings are > listed vertically. How do I convert the ratings into one horizontal row for > each employee? ...

Put Active Worksheet's Name in a Cell #3
Is there a way to put the active worksheet's name in the cell? Not the file name of the whole workbook, just the name of the currently selected sheet. Thanks for your help. Hi Corey, There are a few ways this can be done using either macros or functions, here's one of each: *=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1, LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))) (sourced from Chip Pearson's site, http://www.cpearson.com/excel/excelF.htm) I recommend checking this page out as it also shows ot...

Cannot Create New Data File
I just uninstalled Office 2007 and reinstalled Office 2003. When I try to create a new data file, there are no choices in the dialog box. usually you have the choice of an Outlook 2003 file or an Outlook 97-2002 file. Also, I have no address book choices if I try to create a new address book. I could not find a registry entry for this. Any help would be appreciated. Thanks for your help. Bill Byrnes ...

microsoft.crm.proxy. class which can look up data from specific ob
To look up data from a account, you can use CRMAccount-class, to look up data from a contact, you use CRMContact. This is done by giving the id and you get back the data. But in the header CRM can also give an objectid, which specifies wich object is being used. Is there a class I can use in which you give the object-type and the object-id? So you'll have something like CRMObjects(objecttypecode, objectid). Best Regards! "Chris Plenter" wrote: > To look up data from a account, you can use CRMAccount-class, to look up data > from a contact, you use CRMContact. This...