Hello,

Currently I am working on a project that shows the -/+ of parts made by
certain departments in our facility. For example Department's "A" target was
10 and they only made 5 so they would be -5. This is done for 6 departments
and is recorded on 31 diffrent tabs (for every day of the month) on the same
worksheet. My question is that I would like to use a 3-D reference showing
the running total of -/+ actual achievements. Is there a easier way of than
typing in the formula for all 31 tabs? Typing in that tab 2 looks at tab's
1's data and tab 3 looks at 2's 31 times seems like I am doing this the long
way! Thanks in advance!

If all the sheets to be included the formulaare in are in a row, you could
use a formula like this:
=SUM('Your first sheet name:You thirty-first sheet name'!cell_with_difference)
The single quotations are only necessary if your sheet names contain spaces.

Example, if the difference is in cell A3, and the sheet names are the
defaults:
=SUM(Sheet1:Sheet31!A3)

tj

"Excel'ed Failures" wrote:

Hello,

Currently I am working on a project that shows the -/+ of parts made by
certain departments in our facility. For example Department's "A" target was
10 and they only made 5 so they would be -5. This is done for 6 departments
and is recorded on 31 diffrent tabs (for every day of the month) on the same
worksheet. My question is that I would like to use a 3-D reference showing
the running total of -/+ actual achievements. Is there a easier way of than
typing in the formula for all 31 tabs? Typing in that tab 2 looks at tab's
1's data and tab 3 looks at 2's 31 times seems like I am doing this the long
way! Thanks in advance!

I think I may have misread your post. The formula I post is for summing up
the difference for a division across all 31 sheets.

To write the formula across all 31 sheets at the same time, do this:
Click on the First Sheet.
Shift-Click on the Last Sheet.
You should see the word [Group] on the Title Bar of the Window.
Then, type your formula, and it will go into all of the sheets at the same
time.

Make sure you Deselect the group by Right-Clicking on a Sheet Tab and
clicking Ungroup sheets before you do continue working.

tj

My problem is that the formula will be diffrent for every sheet. The cell
must add up the total from all the previous days. For example sheet "6" will
have to sum up from 1-6. There is a formula needed for every department, I
hope I don't have to write in a formula for every single cell on every sheet
that will take forever T_T

"tjtjjtjt" wrote:

I think I may have misread your post. The formula I post is for summing up
the difference for a division across all 31 sheets.

To write the formula across all 31 sheets at the same time, do this:
Click on the First Sheet.
Shift-Click on the Last Sheet.
You should see the word [Group] on the Title Bar of the Window.
Then, type your formula, and it will go into all of the sheets at the same
time.

Make sure you Deselect the group by Right-Clicking on a Sheet Tab and
clicking Ungroup sheets before you do continue working.

tj

I think I'd try to keep all my data on one worksheet in the workbook. I'd add a
column for Date (and any other indicator I need.)

It makes these kinds of things lots easier. You can do data|subtotals,
data|pivottable, charts and graphs much easier. You could appy data|filter to
see just the stuff you want to see.

And if you had to generate separate worksheets for each
department/week/date/month (whatever), you could create them as report
worksheets (not to be updated, only to be viewed/printed).

Debra Dalgleish has some nice code that does this kind of thing in some sample
workbooks at her site:
http://www.contextures.com/excelfiles.html

Look for:

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb



But if you really want to keep the data separate, you may be able to use some of
the 3D userdefined functions that Myrna Larson and David Hager wrote:.

At John Walkenbach's site:
http://j-walk.com/ss/excel/eee/eee003.txt

(Countif3d/sumif3d/sumproduct3d)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Excel'ed Failures wrote:

Hello,

Currently I am working on a project that shows the -/+ of parts made by
certain departments in our facility. For example Department's "A" target was
10 and they only made 5 so they would be -5. This is done for 6 departments
and is recorded on 31 diffrent tabs (for every day of the month) on the same
worksheet. My question is that I would like to use a 3-D reference showing
the running total of -/+ actual achievements. Is there a easier way of than
typing in the formula for all 31 tabs? Typing in that tab 2 looks at tab's
1's data and tab 3 looks at 2's 31 times seems like I am doing this the long
way! Thanks in advance!

--

Dave Peterson

I don't think you're doing it the long way. Given your layout, you are doing
the only way. If I understand correctly, the formula on sheet 2 is something
like

=SUM(Sheet1:Sheet1!A1)

on Sheet5 it's =SUM(Sheet1:Sheet5!A1)

and on Sheet 31 it's =SUM(Sheet1:Sheet31!A1)

I second Dave Peterson's suggestion that you should put all of the data on one
sheet, in database format, with a column to indicate the date. It will make
your life much simpler in the end <g.


On Sat, 11 Dec 2004 04:35:02 -0800, "Excel'ed Failures" <Excel'ed
wrote:

Hello,

Currently I am working on a project that shows the -/+ of parts made by
certain departments in our facility. For example Department's "A" target was
10 and they only made 5 so they would be -5. This is done for 6 departments
and is recorded on 31 diffrent tabs (for every day of the month) on the same
worksheet. My question is that I would like to use a 3-D reference showing
the running total of -/+ actual achievements. Is there a easier way of than
typing in the formula for all 31 tabs? Typing in that tab 2 looks at tab's
1's data and tab 3 looks at 2's 31 times seems like I am doing this the long
way! Thanks in advance!

"Myrna Larson" wrote...
I don't think you're doing it the long way. Given your layout, you are
doing the only way. If I understand correctly, the formula on sheet 2 is
something like

=SUM(Sheet1:Sheet1!A1)

on Sheet5 it's =SUM(Sheet1:Sheet5!A1)

and on Sheet 31 it's =SUM(Sheet1:Sheet31!A1)

I second Dave Peterson's suggestion that you should put all of the data
on one sheet, in database format, with a column to indicate the date. It
will make your life much simpler in the end <g.
....

Especially since Microsoft has deomonstrated no intention of ever making
Excel a true 3D spreadsheet. There's always OpenOffice Calc, which has
relative worksheets in its 3D references, or 123 or Quattro Pro.

But if one is stuck having to use Excel, there's trickery. Assuming the
workbook in question has been saved, create an ordered list of worksheet
names and name it something WSList. Then create the defined name WSName
referring to the formula

=MID(CELL("Filename",INDIRECT("A1")),
FIND("]",CELL("Filename",INDIRECT("A1")))+1,32)

and use the formula

=SUMPRODUCT(SUMIF(INDIRECT("'"&OFFSET(WSList,0,0,
MATCH(WSName,WSList,0),1)&"'!A1"),"<0"))

Then again, if there's a simple pattern to the worksheet names, and the
cumulative cell is always, say, X99, which adds the value of cell A1 from
the current worksheet to the total from the previous worksheet, it'd be
easier to use something like

=INDIRECT("Sheet"&(SUBSTITUTE(WSName,"Sheet","")-1)&"!X99")+A1