×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Fishing for Ideas on sorting sheets in workbook

Fishing for Ideas on sorting sheets in workbook

Fishing for Ideas on sorting sheets in workbook

(OP)
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

RE: Fishing for Ideas on sorting sheets in workbook

...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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Fishing for Ideas on sorting sheets in workbook

(OP)
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

RE: Fishing for Ideas on sorting sheets in workbook

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

RE: Fishing for Ideas on sorting sheets in workbook

(OP)
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

RE: Fishing for Ideas on sorting sheets in workbook

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

"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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close