Using Excel With A Main Directory

[figuare9]

I have been working in Excel for a few weeks now building a massive website style book. I have ~29 Employees I'm working with, and I'm trying to make an easy way to add/remove employees and data entry throughout the entire sheet. I'm a little lost however, as it's not really a math equation, or a simple formula. It ended up a little bit out of control for me. ha!.

Anyways.. I start in the workbook at essentially a "Home" page. I can click on buttons that hyperlink me all over the place. As of right now, there's well over 200 sheets, so I'm using buttons to navigate all over.

So if I'm at the home page, I have an Employee Directory. My employees are all listed H4:H100. But I'm only using H4:H33 currently. I left quite a bit of room for new employees. As seen here.

So.. If I click on an employee, it then brings me to their own sheet. With personal information in it, along with other employee stuff.

But here's where it gets interesting, and the question I'm looking to hopefully get resolved.


In other places in the workbook, I have calendars with our schedules and things where we assign what the employee is working on.

What I'd like to do is be able to delete an employee in say H4 from the employee directory, and have it update the entire workbook everywhere the employee is.

So let's just say I had a bunch of data entered in Sun-Sat here. If I kill the top employee in the list from the main directory, how could I get excel to shift everything around to accommodate for the removal/addition of employees? All new employees can be added at the bottom of everything. We sort by seniority, so new employees would be at the bottom. But if I have an employee retiring, I'd like to kill them off the list and all of their data.


Right now, I currently tried a formula in B4 (in second picture) that says ='Employee Directory'!H4:H100 It kind of works, but when I delete an employee, it shifts all the other employees up and screws with their data.


Is it possible to do what I'm looking for? My apologies if I didn't explain it well enough.

 

[JLGWhiz]

Re: Help With Using Excel With A Main Directory. See Explanation.

Since there is no 'BeforeDelete' event built in to Excel for data deletions, you would have set up a macro procedure for deletions which would allow the target name to be memorized or remain a static reference. For example, if you want to delete Joe Smith from the list, you could have a certain certain cell where you type in Joe's name and then execute a macro that would use that cell as a reference to look in all your sheets and execute deletions. Or you could use an input box to have the user 3enter Joe's name and initialize a variable to be used by the macro to search the sheets.
Example for cell reference: "Joe Smith" entered in Cell A1 of Master sheet

Dim fn As Range
Set fn = Sheets(1).Range("H:H").Find(Sheets("Master").Range("A1"), , xlValues, xlWhole)
 If Not fn Is Nothing Then
  fn.Delete 'Or fn.EntireRow.Delete
 End If

The foregoing code assumes only one entry per name in the search range.
Example for input box:

Dim fn As Range, nm As String
nm = InputBox("Enter search name, using Proper Case", "SEARCH NAME")
Set fn = Sheets(1).Range("H:H").Find(nm, , xlValues, xlWhole)
 If Not fn Is Nothing Then
  fn.Delete 'Of fn.EntireRow.Delete
 End If

In both cases you would need to ensure that the search value is consistent with how the name will be listed throughout your sheets.
The code above assumes the name will be First Last, in Proper Case with no punctuation. You might have Smith, Joe in some sheets, so that would have to be addressed. The fn variable can be resized in cases where there is more than one row of data, ie.

fn.Resize(2, 1).EntireRow.Delete

would allow two rows to be deleted for Joe.

 

[figuare9]

Re: Help With Using Excel With A Main Directory. See Explanation.

Would this be possible without vba? Not that it's essentially that big of a deal, but I'm extremely inexperienced with it, as well as anyone else that would use this. Also, if I wanted to rename an employee, or change their last name (marriage, spelling, etc) would this also work for that? Would I need to create this vba for every employee, and make changes to their names inside of the vba as well?

 

[JLGWhiz]

Re: Help With Using Excel With A Main Directory. See Explanation.

Would this be possible without vba? Not that it's essentially that big of a deal, but I'm extremely inexperienced with it, as well as anyone else that would use this. Also, if I wanted to rename an employee, or change their last name (marriage, spelling, etc) would this also work for that? Would I need to create this vba for every employee, and make changes to their names inside of the vba as well?

some of what you describe as your objective would require some event code to execute deletions and sheet selection changes. Some of it could be handled with linked cells. But what you are describing is not a simple one formula fits all type of scenario. It is a complex series of actions on multiple sheets. I think you could tackle it better if you do a piece at the time.

 

[figuare9]

Re: Help With Using Excel With A Main Directory. See Explanation.

Would you be interested in guiding me through this? Maybe creating a button, or some kind of method to make this extremely easy to use for the end user? a couple people that will end up using this can barely navigate a webpage. One finger kind of typers. . . lol.

And then there's me, who's never used vba before. So I'm a little stumped on how to set it up.

Workbook layout


Home Page

• Employee Directory
  • Employee
    • Personal Info
      • Personal Info Page
    • Sick Days
      • Sick Day Summary
    • Vacation Days
      • Vacation Day Summary
• Calendar Planner
  • Yearly Calendar
    • January
      • Week 1
      • Week 2
      • Week 3
      • Week 4
      • Week 5
    • February
      • Week 1
      • Week 2
      • Week 3
      • Week 4
      • Week 5
    • etc.

So I have this setup for every week of the year for 2018.. So it'd be nice to definitely be able to change the employees in every week for these..

As much as I'd love to share the document, I just can't with all of the personal info.. But I built a similar sheet..In the sum of like 20 mins, so it's kind of a mock up, with a lot of stuff missing.. But its functional enough to show you what's going on and see the layout.

I have two calendars that we're using for tracking our employees activities.. So that's 52 weeks a year X 2.. Lot of employee editing that I'd like to save myself from changing in the future.
File: http://www.mediafire.com/file/806a0nec9x0n900/Mockup-WB.xlsm