Fishing for Ideas on sorting sheets in workbook

[renigar]

I have a file that is used to track employee time off without preapproval to see trends. It is attached below. It is set up to track for a calendar year. There are two main sheets in the workbook, Hours, where the unapproved time off is input and Summary where you see how many hours were attributed to each time off code. What I would like to do is be able to add or subtract employees from the summary sheet (for new or leaving employees) then sort alphabetically on both sheets. The main issue I have figuring this out is on the Hours sheet each employee has two columns, 1 for hours and 1 for the time off code. How do I sort this and keep the time off codes next to the relevant hours. I think the formulas that sum the hours for the Summary should be SumIfs (maybe, but I was having trouble setting it up). I have SumIf in there now. As a final wish it would be cool to add or subtract an employee on the Summary sheet and have the columns added or subtracted from the Hours sheet when sorted (with a macro). I'm sure this is much easier than I can see at the moment. Your comment or suggestions are appreciated. Note there is a Worksheet_SelectionChange macro on the Hours sheet that can be disabled from cell BL2.

The file (50Kb) link is:
Link

 

[SkipVought]

...and the password is required.

Really do need to see your workbook/worksheet structure.

Maybe upload a version that doesn't require a password.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[renigar]

Sorry Skip,

I thought I had removed it previously. It should work now. Here are a couple of shots you can look at and not open file.

Hours sheet

Summary sheet

 

[SkipVought]

Your problem is in the way that your data is structured. What you have is two report formats and report formats are poor sources for data manipulation.

You need one table with the following fields...

Date
Employee Id
Hours
Off Cd

...and a second table of the work dates in 2023.

Then a simple sort on Transform Query via Microsoft Query, will give you both reports and all you asked for: current employees sorted in whatever order you choose.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!

 

[renigar]

Thanks Skip,

My data layout was great for what it was being used for until I wanted to make it more flexible. Thanks for the tip.

Renigar

 

[SkipVought]

This is when a real database management system is required:
To handle the addition/maintenance/deleting of data
To maintain relational data integrity
To facilitate analysis and reporting

Since this is a relatively simple application, it probably could be done in Excel. I'd guess that your corporate HR system that handles time recording doesn't handle your Off Codes. It's also odd to have dummy employee IDs rather than actual EmpIDs.

The challenge is in maintaining the data: Add/Change/Delete. The two reports that you want to see in the way you want to see, are not a problem as would other analysis and reports yet unknown, IMHO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!