Multiple worksheets & duplicating formula
 

I have 35 worksheets; one w/s for each volunteer Each w/s has the exact
document to capture the number of hours a volunteers works and the type of
service they perform (ex: office work, patient care). I created a work sheet
listing the name of each vol (running vertically) and a row of cells
(horizontially) to capture the total for each month for each vol.

How do I change the formula to between w/s without having to do the = sign
for each cell from all worksheets?

I'm using Excel 2003

Thank you for saving my mind from exploding!
Jackie

Multiple worksheets & duplicating formula
 

Experiment with the Indirect function, you will be able to reference
another worksheet by what ever worksheet name in in a cell

Here is a very simple Sample
http://sites.google.com/site/davesex...attredirects=0

More on Indirect Functions can be found here...
http://www.contextures.com/xlFunctions05.html#RefSheet

Multiple worksheets & duplicating formula
 

Use Indirect as suggested combined with an *array formula* if your
layouts are consistent e.g.
=INDIRECT(A30 & "!B$10:P$10") where A30 contains the Sheet Name and
B10:P10 on each sheet contains the required data


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64285

Multiple worksheets & duplicating formula
 

If the structure of your sheets are same (the columns, rows may have
different size), another solution for getting a summary sheet of all vol. is
to use consolidation under data menu. Consolidate will summarize the data
from different sources to one.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Jackie" wrote:

I have 35 worksheets; one w/s for each volunteer Each w/s has the exact
document to capture the number of hours a volunteers works and the type of
service they perform (ex: office work, patient care). I created a work sheet
listing the name of each vol (running vertically) and a row of cells
(horizontially) to capture the total for each month for each vol.

How do I change the formula to between w/s without having to do the = sign
for each cell from all worksheets?

I'm using Excel 2003

Thank you for saving my mind from exploding!
Jackie

Multiple worksheets & duplicating formula
 

You are my HERO. Thank you!
Jackie

"CurlyDave" wrote:

Experiment with the Indirect function, you will be able to reference
another worksheet by what ever worksheet name in in a cell

Here is a very simple Sample
http://sites.google.com/site/davesex...attredirects=0

More on Indirect Functions can be found here...
http://www.contextures.com/xlFunctions05.html#RefSheet

Multiple worksheets & duplicating formula
 

Thank You for saving my brain from emploding!
Jackie

"mdmackillop" wrote:


Use Indirect as suggested combined with an *array formula* if your
layouts are consistent e.g.
=INDIRECT(A30 & "!B$10:P$10") where A30 contains the Sheet Name and
B10:P10 on each sheet contains the required data


--
mdmackillop
------------------------------------------------------------------------
mdmackillop's Profile: http://www.thecodecage.com/forumz/member.php?userid=113
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=64285

Multiple worksheets & duplicating formula
 

All my worksheets; each tab having the individual name of a volunteer has the
exact same format and cell reference. I looked at the data - consolidate
option and I would greatly appreciate it if you could provide a simple sample
of how to feed all the information from the 35 worksheets to the single
worksheet that captures the total hours for each month. The INDIRECT
formula has saved me many hours...not having to enter "=" and clicking on
each worksheets specific cell I want information from. I'd love to learn of
another option.

Thank you so much for your interest and time.

Jackie

"Khoshravan" wrote:

If the structure of your sheets are same (the columns, rows may have
different size), another solution for getting a summary sheet of all vol. is
to use consolidation under data menu. Consolidate will summarize the data
from different sources to one.
--
R. Khoshravan
Please click "Yes" if it is helpful.


"Jackie" wrote:

I have 35 worksheets; one w/s for each volunteer Each w/s has the exact
document to capture the number of hours a volunteers works and the type of
service they perform (ex: office work, patient care). I created a work sheet
listing the name of each vol (running vertically) and a row of cells
(horizontially) to capture the total for each month for each vol.

How do I change the formula to between w/s without having to do the = sign
for each cell from all worksheets?

I'm using Excel 2003

Thank you for saving my mind from exploding!
Jackie

Multiple worksheets & duplicating formula
 

On Feb 14, 1:13*pm, Jackie wrote:
You are my HERO. *Thank you! *
Jackie

"CurlyDave" wrote:
Experiment with the Indirect function, you will be able to reference
another worksheet by what ever worksheet name in in a cell

Here is a very simple Sample
http://sites.google.com/site/davesex...ples/UsingIndi...

More on Indirect Functions can be found here...
http://www.contextures.com/xlFunctions05.html#RefSheet

It's hard to describe, not knowing what the layout of your worksheet
is. You can loop through the sheets with VBA to get the sheet names
and values from all the other sheets.
Check out this example, enable macros when you open the Workbook.

http://sites.google.com/site/davesex...attredirects=0