Moving Data to Another Worksheet

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!  :)
0
saffron (2)
9/7/2005 11:49:05 AM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
336 Views

Similar Articles

[PageSpeed] 38

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
0
petersod (12004)
9/7/2005 12:50:08 PM
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
> 
0
saffron (2)
9/7/2005 1:51:04 PM
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
0
petersod (12004)
9/7/2005 5:22:16 PM
Reply:

Similar Artilces:

VBA Worksheet Event Change or Selection Question, please assist me
Two fantastic people were kind enough to provide me these two codes below, and now I'm trying to see if I can expand it to include other columns and data in a worksheet. Code 1: Private Sub Worksheet_Change(ByVal Target As Range) Dim myC As Range If Intersect(Target, Range("P:P")) Is Nothing Then Exit Sub Application.EnableEvents =3D False For Each myC In Intersect(Target, Range("P:P")) Range("Q" & myC.Row).Value =3D Date - Target.Value Next myC Application.EnableEvents =3D True End Sub Code 2: Private Sub Worksheet_Change(ByVal Tar...

merging into Word with filtered data
Hello, I'm trying to do a merge in Word using data from Excel. However, I don't want to merge using data from the whole worksheet, but just filtered data (all people from London - place is one of the fields in the worksheet. I have no idea how I might do this. Any help gratefully received. I believe you can accomplish this from Word by using "Query Options" and selecting to merge only those records you're seeking. HTH PC "Italian Pete" <ItalianPete@discussions.microsoft.com> wrote in message news:3E62FCDC-42EC-48DB-92AC-857B224F085F@microsoft.c...

Data Loss in Publisher
Hi I run a W2k network in a school. I have Office Group Policies in place forcing the default save location for Publisher documents (Office XP version) to the pupil's home folders. All my PCs are identical (ghost images). Pupils have roaming profiles (some mandatory). Some users save work in Publisher and the next time they log on to the network and open the saved file, it's contents have gone! There is nothing exceptional about the affected users' user accounts and they have a mixture of mandatory and roaming profiles. Has anyone come across this problem before? Than...

insert worksheet #2
The default for inserting a new worksheet is to the left. Is there a way to have worksheets inserted to the right? Hi Sue in VBA you could use the following macro: sub insert_after() ActiveWorkbook.Sheets.Add After:=ActiveSheet end sub you may apply a button to this macro. -- Regards Frank Kabel Frankfurt, Germany Sue wrote: > The default for inserting a new worksheet is to the left. > Is there a way to have worksheets inserted to the right? put this macro in your personal.xls file and attach it to a keyboard shortcut or toolbar button. Public Sub InsertSheetToRight() ...

merging 3 spreadsheets into one based upon a single common data field
OK I hope you guys can help me out. Here is the scenario. I have 3 spreadsheets. The main spreadsheet I working with has 500 lines with a unique number string. The next two spreadsheets have about 12000 lines but also contain the same unique string as the first spreadsheet. I want to search the 2nd and 3rd spreadsheet using the first spreadsheets unique id number and then take the information from the 2nd and 3rd spreadsheet and then merge it into the first. Does that make since? Any help would be great. -- xchosen --------------------------------------------------------------------...

Any way to clear out stock pricing data before or after going to Quicken?
With all the discussion a few months ago about taking control of our own data, I had gone through the steps to look at my Money file as an Access DB, and found that of my 80MB, more than half was daily stock prices. Thats not something I really want to convert and maintain. I can't imagine that its really needed, since transactions have their own price info. Is there any way to get rid of it in Money, in Quicken, or other? Thanks In Money Plus: Portfolio > Other Tasks > More > Clear historical quotes The process may be different in earlier versions. "JDelMar&q...

moving large amounts of data from one db to another
I have a table that has millions of rows of data in it, which then references another table with millions of referenced (matching by foreign key constraints)... I need to move this data from database A to a new database B, is there any way to do this and preserve the identity columns and their referential integrity?) Thanks! You can't have integrated RI across databases so that part is out. But you can certainly insert and keep the identities. Take a look at SET IDENTITY INSERT in BOL. -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Smokey Grindel"...

Pulling data from multiple worksheets
I have one worksheet with a listing of client numbers and names. I would like to be able to type in a client number on another worksheet and have the client name automatically populate. Look at the Vlookup function or the combination of Index / Match. Vlookup will be a little more straight forward for a first timer but Index / Match is generally speaking a more flexible and less error prone solution. -- HTH... Jim Thomlinson "parthur" wrote: > I have one worksheet with a listing of client numbers and names. I would > like to be able to type in a clie...

rule to move sent mail to 2nd mailbox sent folder
We have Outlook 2002 (enterprise). I have my own mail box and in addition, another shared mailbox. What I need to be able to do is have a rule set up so that when I reply to an email from the shared mail box, that shows the from email address as ''my name and 'on behalf of; 'shared mail box'' it moves the sent email from my own personal email to the sent mail folder on the shared email folder. I also need to set up a rule for any emails that are deleted from the shared email box are put into the 'shared email delete box'. I have looked at the rules and can&#...

summarise data
I have data on a sheet looking like this 5/5/2004 5/5/2004 7/5/2004 7/5/2004 7/5/2004 7/5/2004 8/5/2004 9/5/2004 9/5/2004 9/5/2004 and I want to summarise this into different cells so that it look like 5/5/2004 7/5/2004 8/5/2004 9/5/2004 i.e. I want to list individual occurances of repeating dates o numbers. Any help would be much appreciated -- Message posted from http://www.ExcelForum.com Sounds like a job or a Pivot Table perhaps? http://www.ozgrid.com/Excel/excel-pivot-tables.htm ** Posted via: http://www.ozgrid.com Excel Templates, Training, Add-ins & Business Software Galore...

Restoring Public folders to another server
We have a server that has crashed and AD is all up the wop. It was our exchange server (Exchange 2000). We have a new server in place but we can't restore our public folders from backup (Veritas 9.1). Is there a way to restore public folders to a different server or do we have to rebuild the server with all the same names etc.....? In advance thankyou for the help. -- Network Consultant a full recovery server is your only option, unless you go with a 3rd party tool... "pete" <pete@discussions.microsoft.com> wrote in message news:0DCA3555-EA66-4BCC-AB7C-59896AB02...

Data archive with GP Manufacturing
Has anyone had experience with any data archive with GP Manufacturing. The product from Professional Advantage does not support Manufacturing archive, at least not in the documentation. Don't have an answer for you but we will be doing the same in the next week or so. Will report back when done. "DavidInterDyn" wrote: > Has anyone had experience with any data archive with GP Manufacturing. The > product from Professional Advantage does not support Manufacturing archive, > at least not in the documentation. ...

creating a program that uses data from a sensor....
Hello, I want to create an MFC program that uses parameter values passed to it from a meter (specifically the resistance in ohms of resisters in an electronic circuit). What are the hardware components that would be needed? And what MFC class(es) would I use to accomplish this? Thanks, RABMissouri2006 On 21 Oct 2006 08:10:50 -0700, "RAB" <rabmissouri@yahoo.com> wrote: >Hello, > >I want to create an MFC program that uses parameter values passed to it >from a meter (specifically the resistance in ohms of resisters in an >electronic circuit). What are the ha...

Transfering Outlook Data to a New PC
I've just built a new PC. Currently it has nothing on it other than Windows. When I install Outlook 2003 I'll need to transfer the data from another PC onto it, including all folders, emails and attachments in them and calendar data. What's the easiest and most effective way to do this? Find the PST file and copy it to the new machine and into the appropriate directory, what? Thanks. Copy the pst(s) to the new PC There isnt an appropriate Folder, there is a default folder created when Outlook is installed. http://www.howto-outlook.com/howto/backupandrestore.htm "Marts&q...

Locking of entered data
I haev a form that currently allows you to enter new records and edit existing records. My key field is based on a number entered by the user from another system. Once the number is entered I do not want the user to be able to type over that number but still be aloud to add a new record. I tried the AllowAdditions and AllowEdits in the properties but if I set them to Yes AllowAdditions and No AllowEdits then nothing can be changed. I want them to be able to edit everything but the one field containing the number. Ideas? Glenna, The code I provided would go in the two places that I...

how can I split one cell's data into two cells?
I am using Excel as a database, and have a column with cells with a person's name (text) in it...e.g., John Smith. NOW, after the fact, I wish to have a column with just John in it, and another column with just Smith...but I have NO idea how to do this! Help would be appreciated. Thank you. Please refrain from multiposting, you have answers in one of the 4! newsgroups you posted the same question in. Multiposting is frowned upon and even the MS web interface states so -- Regards, Peo Sjoblom Excel 95 - Excel 2007 Northwest Excel Solutions www.nwexcelsolutions.com "It ...

Losing data in flashdrive
I am experiencing in losing data in flashdrives. It seems that when files are copied onto flashdrive, I should wait for a while before removing flashdrives from USB port. Does anybody know how long should I wait before unplugging flashdrives. Jorge Are you using the Safely Remove Hardware icon in the notification area? If so, it should tell you when you can remove the drive. ....Alan -- Alan Edwards, MS MVP Windows - Internet Explorer http://dts-l.com/index.htm On Mon, 1 Mar 2010 17:29:25 -0700, in microsoft.public.windowsxp.general, "Jorge Cervantes"...

Size of Data input mask
How can I change the size of an Excel Input Mask? Do you mean you want to limit (or even check) the number of characters in a cell when the user is entering data? If yes, take a look at Data|Validation|Settings tab|Allow Text Length If no, then you may want to clarify your question more. Juerg Rohner wrote: > > How can I change the size of an Excel Input Mask? -- Dave Peterson ec35720@msn.com ...

Publisher Data Base
Is there any way to convert an existing excel address list to a Publisher data base for mail merge? Since there are close to 1000 entries I do not want to manually add each one. You don't have to convert anything. Use the Excel list for the mail merge. -- JoAnn Paules MVP Microsoft [Publisher] "Richard Las Vegas" <Richard Las Vegas@discussions.microsoft.com> wrote in message news:909EDBF8-26DC-4661-B36C-1D3FE52CB66E@microsoft.com... > Is there any way to convert an existing excel address list to a Publisher > data base for mail merge? Since there are close t...

Export worksheet to standalone executable?
I have a password-protected worksheet that uses several add-ins many people don't have. Is there a way to export my worksheet to a standalone executable file that people could run even if they don't have Excel or my add-ins? You know, similar to Powerpoint Show (.pps) which allows people to see a ppt even without owning Powerpoint... thanks, Scott -- sdubose99 ------------------------------------------------------------------------ sdubose99's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27538 View this thread: http://www.excelforum.com/showthread....

Extract Information To Another Worksheet
Hi All, I have a worksheet with Colonm Headings and Data below each heading, except the "Column A" (in some cases it has and in some cases it doesnt). I want to extract all rows where there is data in "Column A" to a separate worksheet. The Entire Row should be copied to the new Worksheet once i enter a value in Column A for each record and the new sheet should be frequently updated from the original list, its like a building list. -- M Imran Buhary In a separate, helper, column in row 2 enter: =--ISBLANK(A2) and copy down Then switch on Autofilter and set this n...

Can databases be merged to update data?
Recently, two employees worked on the same database at two different locations on two different workstations. After we entered data into the databases, we cannot seem to merge our information back into one database. Atleast the help files didn't provide a solution here. Can anyone direct us to the appropriate newsgroup if this isn't the site for the answer we are seeking? If you can answer our question and provide us with the information to merge our databases into one of the databases, please help. Thank you in advance. Hi Mark, Working with copies of your databases, u...

Data table vs Axis value abbreviations
Windows 2000 Office 2000 I have a chart with the data table showing. The values go into the millions. On the axis, I'd like to use e.g. $1M instead of $1,000,000. Is there a way that I can maintain the actual value in the data table, while abbreviating the axis value? Thanks in advance for your help Rita Palazzi Senior Engineer/ FedEx Express Never mind. Someone showed me the "Display Units" on the axis format menu and I was able to do what I needed. Hope everyone has a GREAT Thanksgiving! Rita Palazzi wrote: > Windows 2000 > Office 2000 > > > I hav...

How to Get Numerical Data
I am converting a VB6/ADO/Access application to .NET and I want to convert the data received to XML. The data acts as the database source to some Crystal Reports 10 reports. In the old system, the value fields were recognized as decimal values by CR and they were formatted as xx.xx for output. With XML, everything is string data. (I have an XSD file that sets criteria such as decimal or integer but that doesn't change what CR sees when it reads the data. Is there a way to handle this? Wayne ...

Add rows on worksheet by date?
I've been asked to create a spreadsheet that will track vehicle miles by route. The drivers will note the odometer when they start the route and the odometer when they return, so we can log that across a row: Vehicle - Route - Odo Start - Odo End - Calc Route Miles They also want miles by vehicel by day. Since a vehicle can travel more than one route in a day, is there an easy way to do this? My original thought was to have input through validation drop-downs to choose teh vehicle and the route - now I'm thinking of using a Form, and the Enter Data button will automatically sort by...