EXCEL: Several sheets for each week, names can change each week.

[jongag1]

Hello,

We have an Excel file with, for each week, a different sheet.
In each sheet are names of employees in it in a colomn. The names (unfortunately) can change each week. After each name is a figure telling how many vacation hours they have build up that week and a figure telling how many hours vacation they took that week.

I am trying to insert a sheet telling me the total build up vacation hours and the total of taken vacation hours for each employee.
The problem I have is that on row 3 can be an employee with the name John Smith in week 30 and that in week 31 on that row is a new Employee with the name Ferry Smith and that John Smith is at row 4. It can also be that John Smith wil leave in week 31 so his name is not present anymore in week 32.

I hope you all understand what I mean. If not please let me know, now nobody may go on holiday

Is there an easy solution for this?

Kind regards,
Age

 

[xlbo]

Firstly, you are making life hard for yourself by keeping the data on seperate sheets.

Your 1st step should be to try and append each weekly file to a master spreadsheet which contains data for all weeks. As long as the number of columns and formatting is consistent, you should be ok simply copy / pasting it. Once you do this, there are a LOT of different options for aggregating / summarising data....

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[SkipVought]

Age,

I strongly support Geoff's suggestion.

Unfortunately, it is a mistake that many spreadsheet users make, simply because they have not yet learned how tables work. Excel has many tools for table analysis and reporting that will make your life easier than if you proceed down the current path.

What are LIST & TABLE PRINCIPLES for Spreadsheet Users faq68-5184

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[jongag1]

Allright Skip and Geoff,

Will try to redesign it, thanks for your support.

Regards,
Age

 

[KenWright]

As long as you have a unique ID for each employee, then once you get it all on one sheet you can use a Pivot Table to do what you want in a heartbeat. You can show Vacation accrued and vacation taken, by employee and by time period, which can be by the lowest level of date you have in your data, or can be grouped into Months / Quarters or years or any combination, in literally seconds.

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlbo]

Just finished work Ken ??

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[KenWright]

LOL - Nah, but my head can't take any more today, and so I've pretty much switched off so figured I'd flip over on to here instead

Think I'll pack up and go home now though.

Regards
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------