Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Fishing for Ideas on sorting sheets in workbook

Status
Not open for further replies.
renigar

renigar

Technical User
Jan 25, 2002
111
US
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
 
Sort by date Sort by votes
...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,

[glasses]Just 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!
 
Upvote 0 Downvote
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
Hours_kp1o5l.jpg


Summary sheet
Summary_nghtfd.jpg
 
Upvote 0 Downvote
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,

[glasses]Just 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!
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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,

[glasses]Just 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!
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Solved
Combine two working macros into one for MS Word 1
Replies
7
Views
1K
strongm
strongm
2009luca
  • Question
put value in input box web page
Replies
0
Views
176
2009luca
2009luca
sal21
  • Locked
  • Question
scrping in listbox in a web page
Replies
0
Views
593
sal21
sal21
Andrzejek
  • Locked
  • Question
Hyperlinks in Word cut at #
Replies
2
Views
436
Andrzejek
Andrzejek
R
  • Locked
  • Question
reversed words in english leading new english words
Replies
10
Views
558
Chriss Miller
C

Part and Inventory Search

Sponsor

Back
Top