Is it possible to have the data in a cell on one worksheet automatically copy
itself to another worksheet in the same document?

For example, if I have seperate worksheets for each week in a month listing
income for each day of that week, and the total income for that week tallied
up...can the cell containing the sum for that week be automatically copied to
a seperate worksheet where the weekly totals are being compared to each other?

That way if I change a daily amount, it will not only update the total for
that week on the weekly worksheet...but also update the weekly total on the
monthly worksheet.

It seems like this would be a very useful function, but I haven't found any
information on whether it is possible and how to accomplish it.

Thank you so much for any suggestions...it is driving me nuts! :)

There's nothing built into excel that does this. You could write a macro that
examines the field and moves the data over to that other sheet. I'd do it after
all the data entry so I could verify my changes before they go to the wrong
sheet.

But if I were you, I'd add a column indicator for the week number. Then I'd use
data|filter|autofilter (or data|sort) to view just the data I wanted. By
keeping the data in one spot, you won't ever have to deal with discrepancies
between your two lists.

===
If you really want a macro, you may find something close at:

Debra's site:
http://www.contextures.com/excelfiles.html

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

or

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

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

Crimsann wrote:

Is it possible to have the data in a cell on one worksheet automatically copy
itself to another worksheet in the same document?

For example, if I have seperate worksheets for each week in a month listing
income for each day of that week, and the total income for that week tallied
up...can the cell containing the sum for that week be automatically copied to
a seperate worksheet where the weekly totals are being compared to each other?

That way if I change a daily amount, it will not only update the total for
that week on the weekly worksheet...but also update the weekly total on the
monthly worksheet.

It seems like this would be a very useful function, but I haven't found any
information on whether it is possible and how to accomplish it.

Thank you so much for any suggestions...it is driving me nuts! :)

--

Dave Peterson

Thanks for responding so quickly, and for your advice. The information has
to be on seperate worksheets so it looks like it is going to have to be a
macro if it happens at all.

I have never worked with macros before, but I get the basic gist of it. I
tried setting one up that copied a cell on worksheet 1 and pasted into a cell
on worksheet 2. I figured if I could get it to work on a simple one cell to
one cell, I could work on it from there.

The total on worksheet 1 was say $160.00, that amount was copied onto
worksheet 2 when I set up the macro. Then I changed the amount on worksheet
1 to $200.00 and ran the macro assuming it would copy the $200.00 and paste
in onto worksheet 2. Instead, worksheet 2 now shows an empty cell.

Obviously it did something, as there was a change of some sort made, just
not the result I was hoping for.

When I did the copy and paste, I selected to paste the value of the original
cell, would that have anything to do with the result I got.

Or am I going about this macro the wrong way.

"Dave Peterson" wrote:

There's nothing built into excel that does this. You could write a macro that
examines the field and moves the data over to that other sheet. I'd do it after
all the data entry so I could verify my changes before they go to the wrong
sheet.

But if I were you, I'd add a column indicator for the week number. Then I'd use
data|filter|autofilter (or data|sort) to view just the data I wanted. By
keeping the data in one spot, you won't ever have to deal with discrepancies
between your two lists.

===
If you really want a macro, you may find something close at:

Debra's site:
http://www.contextures.com/excelfiles.html

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

or

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

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

Crimsann wrote:

Is it possible to have the data in a cell on one worksheet automatically copy
itself to another worksheet in the same document?

For example, if I have seperate worksheets for each week in a month listing
income for each day of that week, and the total income for that week tallied
up...can the cell containing the sum for that week be automatically copied to
a seperate worksheet where the weekly totals are being compared to each other?

That way if I change a daily amount, it will not only update the total for
that week on the weekly worksheet...but also update the weekly total on the
monthly worksheet.

It seems like this would be a very useful function, but I haven't found any
information on whether it is possible and how to accomplish it.

Thank you so much for any suggestions...it is driving me nuts! :)

--

Dave Peterson

If you've never worked with macros before, you may want to post more details and
the code you tried.

Or maybe add a column that uses
=text(a2,"yyyymm")
that can be used to determine the month/worksheet
and try Ron de Bruin's addin.



Crimsann wrote:

Thanks for responding so quickly, and for your advice. The information has
to be on seperate worksheets so it looks like it is going to have to be a
macro if it happens at all.

I have never worked with macros before, but I get the basic gist of it. I
tried setting one up that copied a cell on worksheet 1 and pasted into a cell
on worksheet 2. I figured if I could get it to work on a simple one cell to
one cell, I could work on it from there.

The total on worksheet 1 was say $160.00, that amount was copied onto
worksheet 2 when I set up the macro. Then I changed the amount on worksheet
1 to $200.00 and ran the macro assuming it would copy the $200.00 and paste
in onto worksheet 2. Instead, worksheet 2 now shows an empty cell.

Obviously it did something, as there was a change of some sort made, just
not the result I was hoping for.

When I did the copy and paste, I selected to paste the value of the original
cell, would that have anything to do with the result I got.

Or am I going about this macro the wrong way.

"Dave Peterson" wrote:

There's nothing built into excel that does this. You could write a macro that
examines the field and moves the data over to that other sheet. I'd do it after
all the data entry so I could verify my changes before they go to the wrong
sheet.

But if I were you, I'd add a column indicator for the week number. Then I'd use
data|filter|autofilter (or data|sort) to view just the data I wanted. By
keeping the data in one spot, you won't ever have to deal with discrepancies
between your two lists.

===
If you really want a macro, you may find something close at:

Debra's site:
http://www.contextures.com/excelfiles.html

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

or

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

or maybe Ron de Bruin's easyfilter.
http://www.rondebruin.nl/easyfilter.htm

Crimsann wrote:

Is it possible to have the data in a cell on one worksheet automatically copy
itself to another worksheet in the same document?

For example, if I have seperate worksheets for each week in a month listing
income for each day of that week, and the total income for that week tallied
up...can the cell containing the sum for that week be automatically copied to
a seperate worksheet where the weekly totals are being compared to each other?

That way if I change a daily amount, it will not only update the total for
that week on the weekly worksheet...but also update the weekly total on the
monthly worksheet.

It seems like this would be a very useful function, but I haven't found any
information on whether it is possible and how to accomplish it.

Thank you so much for any suggestions...it is driving me nuts! :)

--

Dave Peterson


--

Dave Peterson