Link a macro to a cell value
 

I have a workbook with a varying number of worksheets.

Each sheet has a "sheet total" cell that sums a range
of "quantity" cells. The first worksheet has a "project
total", which will contain the sum ot the sheet totals for
the entire workbook. I have the function for that task.

I'd like the "project total" function (macro) to run
whenever any of the "sheet total" values change.

how do I associate the macro to that event?

Thanks in advance,
Rich

Link a macro to a cell value
 

Hi Rich,
As I understand your question your master sheet shows only one
total from the other sheets -- no individual sheet totals.
You will have to be able to find those totals; hopefully, they
are in the same location on each worksheet.

You would use a Calculate event
http://www.mvps.org/dmcritchie/excel/event.htm

You might be able to eliminate use of a macro entirely if
you can use a 3-D total see Excel Help, Answer wizard: 3-D sum
one such topic you will find is (you can use 3D or 3-D in the above search)
Refer to the same cell or range on multiple sheets by using a 3-D reference.

Things you do not mention. Adding additional worksheets, is the sum
on the same location on each sheet.

You might also take a look at
http://www.mvps.org/dmcritchie/excel/buildtoc2.htm
which handles pulling off descriptions and totals from specific
locations of named worksheets. Don't think it applies to your
request but you might find it interesting for related usage.
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Rich" wrote in message ...
I have a workbook with a varying number of worksheets.

Each sheet has a "sheet total" cell that sums a range
of "quantity" cells. The first worksheet has a "project
total", which will contain the sum ot the sheet totals for
the entire workbook. I have the function for that task.

I'd like the "project total" function (macro) to run
whenever any of the "sheet total" values change.

how do I associate the macro to that event?

Thanks in advance,
Rich

Link a macro to a cell value
 

Why are you using a macro for anything?

Assuming that the individual sheets have their total in cell C28, on
the summary worksheet, use =SUM(Sheet1:Sheet10!C28)

where Sheet1 and Sheet10 are the first and last worksheets of interest
to you.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I have a workbook with a varying number of worksheets.

Each sheet has a "sheet total" cell that sums a range
of "quantity" cells. The first worksheet has a "project
total", which will contain the sum ot the sheet totals for
the entire workbook. I have the function for that task.

I'd like the "project total" function (macro) to run
whenever any of the "sheet total" values change.

how do I associate the macro to that event?

Thanks in advance,
Rich

Link a macro to a cell value
 

The number of worksheets will vary from workbook to
workbook.

As for "macros", perhaps I mis-use the term, but I can run
the functions from the "macro" dialog, if I desire...

Rich
-----Original Message-----
Why are you using a macro for anything?

Assuming that the individual sheets have their total in
cell C28, on
the summary worksheet, use =SUM(Sheet1:Sheet10!C28)

where Sheet1 and Sheet10 are the first and last
worksheets of interest
to you.

--
Regards,

Tushar Mehta, MS MVP -- Excel

Link a macro to a cell value
 

It doesn't matter if the number of sheets vary. Just use two dummy
sheets, say SheetFirst and SheetLast. Put all other other sheets in
between these two. Your formula should be
=SUM(SheetFirst:SheetLast!C28)

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
The number of worksheets will vary from workbook to
workbook.

As for "macros", perhaps I mis-use the term, but I can run
the functions from the "macro" dialog, if I desire...

Rich
-----Original Message-----
Why are you using a macro for anything?

Assuming that the individual sheets have their total in
cell C28, on
the summary worksheet, use =SUM(Sheet1:Sheet10!C28)

where Sheet1 and Sheet10 are the first and last
worksheets of interest
to you.

--
Regards,

Tushar Mehta, MS MVP -- Excel