Worksheet Function & Syntax Question

[Roy]

Situation: Excel Worksheet "A" contains columns
representing each category of transmittals being
forwarded. . This form is used each time a transmittal is
made and ONLY CONTAINS data in the cells applicable to the
categories being transmitted. Transmittals from each sub-
ordinate unit are entered in separate rows. The data
entered is numeric in nature. Once the data for the
transmittal is completed and printed, the data is deleted,
and the form is used again for the next transmittal.

Excel Worksheet "B" also contains columns
and cells identical to those outlined above. The data
entered in these cells is also numeric in nature; however,
this data must be a "cumulative" sum of all transmitted
data..

Problem: I have been able to "link" applicable
cells on Worksheet A & B; However, I do not know how to
code the applicable cells to keep a cumulative total of the
(one-time) entries in Worksheet A on Worksheet B, without
the totals starting over each time I delete data in
Worksheet A.

Question: If this can be done, what Function and
Syntax should I use {on which worksheet}?

Thank you very much I would greatly appreciate any
assistance.

 

[Ken Wright]

You are really trying to make things hard for yourself, and you are also dispensing with any means
of auditability or error checking. What you have asked can be achieved, but there are few if any
on the group that would do such a thing. Bear with me whilst I suggest a possible different
method, and see if it helps at all:-

Create a single file with a start and a finish sheet, and a summary sheet that adds up all data
between them. Then create as many individual sheets between start and finish as you like for each
new transmittal. This gives you the single sheet you need for the transmittal, gives you the
summary of all that has gone before, and also gives you an audit trail that allows you to check
back to any point you like. If this sounds feasible to you then the following will give you a bit
more detail:-

Create your file and get one sheet exactly the way you want it to be (Take your time and get it
right, because now is the time to do this) - Now right click on the tab and select move or copy
and then tick the little box that says create a copy. Now repeat that until you have as many
sheets as you need PLUS at least 3 more. You can select say 2,3,4,5,6,7.... sheet tabs at once
and then do move or copy..... and it will copy as many as you have selected. Now ignore the first
2 sheets and start naming them all say Week 1, Week 2 or a,b,c etc and so on. Name the first
sheet Summary, the second sheet START, and your very last sheet FINISH.

Now click on your Summary sheet and in A1 put =SUM(START:FINISH!A1) which will pull in any data
from A1 pulling from START
sheet to FINISH sheet and including any sheets in between. As long as any sheets you add to the
file are in between START and FINISH sheets, they will get pulled into the total, no matter what
you call them. Now hide your START and FINISH sheets.

Copy the cell A1 and then paste special as formulas into any cells that you need to add up all the
sheets on the Summary page.