BleepingComputer.com: Microsoft Excel: Automating Entries Between Sheets?

Hi Anyone of an Excel Nature,

I have a question for anyone versed in using excel.

I keep a months records of my hours that I work as apartment manager for the owner on a single excel sheet.

However, the owner wants weekly time sheets and he wants them sent to him now.

I have at least half a years monthly time sheets that he wants in a weekly format.

I would still like to keep my records of my hours in a monthly format so that I can see what I've done at an entire month's glance.

So, my question is does anyone know of a way to automate the excel monthly sheet so that when I enter data into the monthly sheet that it automatically duplicates itself in the weekly sheets?

It is kind of a lot of copy and pasting other wise.

If you don't know maybe you know where there might be an answer?

Thanks.

John

This post has been edited by Johnz414: 07 January 2008 - 04:57 PM

Yes, but not having used Excel in this way for about 5 years I can't recall the exact details.

It has to do with linking sheets. Here's how I seem to recall doing it:
- go to the new sheet and select a cell and type "=" (without the quotes)
- go to the old sheet and click on the cell that has the data that you want on the new sheet
- go back to the new sheet and you'll see that it has the Sheet number, an !, and the cell reference from the old sheet. (my Excel screwed up the Sheet number so I had to change it manually).
- press Enter and you'll see the sheet's contents reflected there (there may be some difficulty with the cell formatting tho')

Once you've achieved that you can use it to start new calculations.
There's also a way to enter the calculations with the cell reference embedded so that the data from above isn't needed on the sheet (but that'll take some research to do).

This post has been edited by usasma: 08 January 2008 - 09:14 AM

Usasma, yeah, your memory is correct, that'll work

Another way, without any data duplication, is filtering.
Keep all the data on one sheet, contiguous rows and columns - meaning no blank rows, no blank columns. Assuming you have heading, use Data, then Auto filter menu. That will put little dropdown arrows on every heading. Click the arrow, select Custom filtering from the dropdown for whatever you want to select. Print the filtered data. Select All when done and the data is intact and you didn't change the file size.

You may want to format the data sheet for printing in a way that will indicate today's date, you can put such information in the header, and embelish it with fancy formatting, for the manager to admire

There's also Advanced filter mode - read Help.

OK, I've checked out both approaches described here. They both show some promise.

But the filtering method I don't think will serve my purpose. It's nice but the owner wants a record of every day/week even if I don't do any work on certain days. So there are going to be blank rows at least in the copy for him. He wants to see everything to get an idea of how I'm using my time.

And I've tried the method put forth by Usasma only to find that it's pretty much the same thing as copy and paste. IE., it doesn't work like a "Formula" where when I enter data on the "Monthly" sheet (after setting up the "Formula" on a template) then the data automatically gets copied to the weekly sheets from the Monthly sheet.

I'm looking more for a "Formula" that permanently reduces my work load. I just make a template and wallah when I keep track of my hours on the monthly sheet the data gets duplicated automatically to the weekly sheets. Also, I need and would like it if I change data on the monthly sheet then it automatically updates on the weekly sheets.

I already know that I can copy and paste.

Oh, I also would like to do the same thing with the "descriptions" - what I write up to describe what I did during the hours worked that I include on my work sheets.

That would be when I write up the descriptions on the monthly sheet then it gets duplicated automatically onto the weekly sheets.

Of course if I'm doing something wrong please do Enlighten me.

Here is the "formula"? that I've ended up using to duplicate what Usasma advised me to try: "='[Nov 2007]Nov 2007'!F13" (without the quotes).

1. "Nov 2007" is the monthly sheet that I'm copying data from and "Week 1" is the first weekly sheet that I'm copying the data too.

2. "Weekly 1" is conspicuously absent in the formula however the data does transfer when setting this "formula"? up in "Weekly 1".

3. However, the data will not update automatically on the weekly sheet when I change it on the monthly sheet. i need/want it to update automatically.

The question then is this the right formula: "='[Nov 2007]Nov 2007'!F13"? ("F13" being the column and row of the data being duplicated).

Does that help?

John

It'll take some experimentation to do what you want - but it is possible. You'll have to mess with the formatting and the formula's to get it to work. In this case the Help files are your best asset. Also, you can try this Google search to see if there's more info out there: http://www.google.com/search?q=excel+suppo...lient=firefox-a