Linking worksheets

[Kevin]

I am trying to make a workbook with 4 worksheets, each
being 1 year. In the first column will be a number in the
second column will be the total of the first column for
the last 365 days. How do I link the sheets so I can have
a total for the last 365 days? I know how to do it if it
was on the same worksheet but I can not figure out how to
link the two to do what I want.

Thanks
Kevin

 

[Ragdyer]

If I understand you correctly, you have, for example,
In all 4 sheets,
B52 has a formula:
=SUM(A1:A52)

Try this:
Right click in B52 of Sheet1, and choose "Copy".
Click on the Sheet2 tab, and right click in *B53*,
Choose "PasteSpecial", then click on "PasteLink".

You should now have the total from Sheet1 displayed in B53 of Sheet2.
Any change on Sheet1 to B52 will automatically be reflected in B53 of
Sheet2.

You can now do the same to the other three sheets.

Is this what you're looking to accomplish?

 

[Kevin]

Hey thanks for the help, but its not exactly what I am
after. I have 4 worksheets, each is one year 2003 through
2006. Column A is dated Jan 1st- Dec 31st. Column B is a
number that is entered daily. In column C I would like a
total for the last 365 days of column B. For example if I
am to enter 5 in column B on October 13 2004 I would like
column C to add 5 to the total and minus the amount
entered on October 13 2003(which is on another
worksheet.) I would like to have a constant running total
of column B for any given 365 consecutive days.

Thanks Kevin

 

[Ragdyer]

With this better explanation of what you're looking for, this is one
approach you could try.

Add an additional column to your scenario.
Say your sheets are labeled respectively,
2003,
2004,
2005,
and 2006.

Assume A1 is 1/1/03, and A365 is 12/31/03.
Enter this formula in C1 of 2003:

=IF(B1<>0,SUM($B$1:B1),"")

And copy down to C365.
This gives you a running total of 2003.
Since there's no 2002, this is all you need to see day to day of 2003.

In 2004, enter the same formula in C1:

=IF(B1<>0,SUM($B$1:B1),"")

And enter this formula in D1:

=IF(C1<>"",'2003'!$C$365+C1-'2003'!C1,"")

Select both C1 and D1, and drag down to copy to row 366 (leap year this
year).

Now, every time you make an entry to column B, column C will display the
year-to-date running total, and column D will display the last 365 day
running total.

Just change your references, and duplicate these formulas for the other two
sheets.

 

[Kevin]

That's not working either, I can get it to work by using
this formula =SUM('2003'!B13:B376)+('2004'!B12) in column
C12 which is Jan 1st of 2004 in C2 =SUM('2003'!B14:B376)+
('2004'!B12)+('2004'!B13) the problem is that I have to
type this formula and change it for every day for each
day for the 4 years. If I copy down for the whole year, I
get this
In C13 =SUM('2003'!B14:B377)+('2004'!B13)
In C14 =SUM('2003'!B15:B378)+('2004'!B14)
In C15 =SUM('2003'!B16:B379)+('2004'!B15)
And so on. This would work if all 4 years were on one
worksheet, but there is no info on B377, B378, and B379
ECT ECT. December 31 2003 ends on row B366 and I cannot
figure out how to tell the formula that it ends and to
now go to worksheet 2004 for the rest of the numbers.

 

[RagDyer]

When you say "That's not working either", what exactly do you mean?

The arithmatic *is* correct with the formulas I suggested!

Can you tell me what you got, as opposed to what you expected to get?
--


Regards,

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

That's not working either, I can get it to work by using
this formula =SUM('2003'!B13:B376)+('2004'!B12) in column
C12 which is Jan 1st of 2004 in C2 =SUM('2003'!B14:B376)+
('2004'!B12)+('2004'!B13) the problem is that I have to
type this formula and change it for every day for each
day for the 4 years. If I copy down for the whole year, I
get this
In C13 =SUM('2003'!B14:B377)+('2004'!B13)
In C14 =SUM('2003'!B15:B378)+('2004'!B14)
In C15 =SUM('2003'!B16:B379)+('2004'!B15)
And so on. This would work if all 4 years were on one
worksheet, but there is no info on B377, B378, and B379
ECT ECT. December 31 2003 ends on row B366 and I cannot
figure out how to tell the formula that it ends and to
now go to worksheet 2004 for the rest of the numbers.

 

[Kevin]

Well that is sort off working, the problem is that with
the fromula you gave me there has to be a value greater
than zero for any given day or I get this in column D eg
if I enter numbers greater than 0 from Jan 1st 2003 till
Jan 31st 2003 then I enter 0 or nothing on the 1st of
October In column D On October 1 of 2004 I get #VALUE

-----Original Message-----
When you say "That's not working either", what exactly do you mean?

The arithmatic *is* correct with the formulas I suggested!

Can you tell me what you got, as opposed to what you expected to get?
--


Regards,

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

That's not working either, I can get it to work by using
this formula =SUM('2003'!B13:B376)+('2004'!B12) in column
C12 which is Jan 1st of 2004 in C2 =SUM('2003'!B14:B376)+
('2004'!B12)+('2004'!B13) the problem is that I have to
type this formula and change it for every day for each
day for the 4 years. If I copy down for the whole year, I
get this
In C13 =SUM('2003'!B14:B377)+('2004'!B13)
In C14 =SUM('2003'!B15:B378)+('2004'!B14)
In C15 =SUM('2003'!B16:B379)+('2004'!B15)
And so on. This would work if all 4 years were on one
worksheet, but there is no info on B377, B378, and B379
ECT ECT. December 31 2003 ends on row B366 and I cannot
figure out how to tell the formula that it ends and to
now go to worksheet 2004 for the rest of the numbers.

-----Original Message-----
With this better explanation of what you're looking for, this is one
approach you could try.

Add an additional column to your scenario.
Say your sheets are labeled respectively,
2003,
2004,
2005,
and 2006.

Assume A1 is 1/1/03, and A365 is 12/31/03.
Enter this formula in C1 of 2003:

=IF(B1<>0,SUM($B$1:B1),"")

And copy down to C365.
This gives you a running total of 2003.
Since there's no 2002, this is all you need to see day to day of 2003.

In 2004, enter the same formula in C1:

=IF(B1<>0,SUM($B$1:B1),"")

And enter this formula in D1:

=IF(C1<>"",'2003'!$C$365+C1-'2003'!C1,"")

Select both C1 and D1, and drag down to copy to row 366 (leap year this
year).

Now, every time you make an entry to column B, column C will display the
year-to-date running total, and column D will display the last 365 day
running total.

Just change your references, and duplicate these formulas for the other two
sheets.
--
HTH,

RD

--------------------------------------------------------

- - -

---

-
.

 

[RagDyer]

You can try replacing the formula in Column C with this one:

=IF(B1<>"",SUM($B$1:B1),"")

This will eliminate the error, *BUT*, you will have to enter a zero in
Column B when there is no number to enter for a particular day.
Leave the Column B cell empty (blank) only when you have not yet come to
that day chronologically.

--

HTH,

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

Well that is sort off working, the problem is that with
the fromula you gave me there has to be a value greater
than zero for any given day or I get this in column D eg
if I enter numbers greater than 0 from Jan 1st 2003 till
Jan 31st 2003 then I enter 0 or nothing on the 1st of
October In column D On October 1 of 2004 I get #VALUE

-----Original Message-----
When you say "That's not working either", what exactly do you mean?

The arithmatic *is* correct with the formulas I suggested!

Can you tell me what you got, as opposed to what you expected to get?
--


Regards,

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

That's not working either, I can get it to work by using
this formula =SUM('2003'!B13:B376)+('2004'!B12) in column
C12 which is Jan 1st of 2004 in C2 =SUM('2003'!B14:B376)+
('2004'!B12)+('2004'!B13) the problem is that I have to
type this formula and change it for every day for each
day for the 4 years. If I copy down for the whole year, I
get this
In C13 =SUM('2003'!B14:B377)+('2004'!B13)
In C14 =SUM('2003'!B15:B378)+('2004'!B14)
In C15 =SUM('2003'!B16:B379)+('2004'!B15)
And so on. This would work if all 4 years were on one
worksheet, but there is no info on B377, B378, and B379
ECT ECT. December 31 2003 ends on row B366 and I cannot
figure out how to tell the formula that it ends and to
now go to worksheet 2004 for the rest of the numbers.

-----Original Message-----
With this better explanation of what you're looking for, this is one
approach you could try.

Add an additional column to your scenario.
Say your sheets are labeled respectively,
2003,
2004,
2005,
and 2006.

Assume A1 is 1/1/03, and A365 is 12/31/03.
Enter this formula in C1 of 2003:

=IF(B1<>0,SUM($B$1:B1),"")

And copy down to C365.
This gives you a running total of 2003.
Since there's no 2002, this is all you need to see day to day of 2003.

In 2004, enter the same formula in C1:

=IF(B1<>0,SUM($B$1:B1),"")

And enter this formula in D1:

=IF(C1<>"",'2003'!$C$365+C1-'2003'!C1,"")

Select both C1 and D1, and drag down to copy to row 366 (leap year this
year).

Now, every time you make an entry to column B, column C will display the
year-to-date running total, and column D will display the last 365 day
running total.

Just change your references, and duplicate these formulas for the other two
sheets.
--
HTH,

RD

--------------------------------------------------------

- - -

---

-
.