Use one spreadsheet to update another spread column

[suchis]

I have two spreadsheets. One contains Weekly information such as Number of hours worked and the other contains Monthly information. I want the user to be able to enter hours into the Weekly spreadsheet after which time the Monthly spreadsheet will contain the totals for each week. Therefore, the spreadsheets look like this:

Day1 Day2 Day3 Total
Reg. Overtime Reg. Overtime Reg. Overtime Reg. overtime
6 3 2 7 4 2 12 12



Week1 Week 2 Week3
Reg. Overtime Reg. Overtime Reg. Overtime
12 12

Therefore, I need to how to allow the user to use only ONE spreadsheet, the weekly spreadsheet, to store data in the Monthly spread sheet "Week 1 ..." Column.

 

[SkipVought]

Hi,

It is REALLY BAD spreadsheet design to chop SIMILAR DATA up into groupings of data -- like week, month, quarter, year, region, salesperson, product area etc, etc, etc!

If ALL your data were in ONE table that included DATE, you could VERY EASILY Summarize your data using a number of Excel summarizing/reporting techniques, among which are...

Sort
Filter
Subtotal
PivotTable
Chart

For instance, generating a report by Week (takes a spreadsheet formula), Month, Quarter etc can be accomplished in LESS THAN ONE MINUTE from a single, well designed table.

Post an example of your data, and I'll explain how.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[bluedragon2]

open both spreadsheets and on the month, put = then click on the weekly sheets cell where the total is and then hit enter. This will show you the syntax...



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[bluedragon2]

Actually that only works between sheets forget that, and also listen to Skip, as always, he is correct.



[Blue]Blue[/Blue] [Dragon]

If I wasn't Blue, I would just be a Dragon...

 

[SkipVought]

Blue,

Unfortunately, I am not ALWAYS correct. Guys like you, help keep me straight!

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[suchis]

Thank you for your response. What I am actually trying to do is allow each employee to enter their hours worked unto on time sheet on a weekly basis, and at the end of the month they will print one time sheet, which would be the monthly spreadsheet. At the moment the employees will have to print the weekly time sheet every week so that the information can be stored into a separate database. However, by changing from the weekly time sheet to the monthly time sheet it will save us time since we will only have to enter the data once a month instead of 4 times. Moreover, the managers want to be able to track what each employee is doing on a weekly basis, hence the weekly time sheet.

 

[SkipVought]

The Weekly Timesheet is a DATA ENTRY means to get employee time worked per day into a database/table.

You cannot update another sheet the way that you envision using worksheet functions. This can only be done via code.

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884