Solved

Create an automated Employee Database in Excel

Posted on 2010-08-26
35
597 Views
Last Modified: 2012-05-10
I've created an employee database in excel for the purpose of importing and linking the document to a Visio Org. Chart.

The first worksheet is a MASTER and contains all employee info, all other worksheets and the respective cells are linked to the MASTER sheet.

I'm looking for greater automation when it come to deleting or adding an employee to the MASTER sheet. Currently, when an employee is deleted a "#REF!" appears everywhere that employee was linked. When an employee is added you have to insert a row on the appropriate worksheet and paint down the value for the added row (employee).

I would like these changes to occur automaticially, or with the aid of a macro, I would like to keep the database outside of Access.
0
Comment
Question by:-Polak
  • 16
  • 16
  • 2
  • +1
35 Comments
 
LVL 45

Expert Comment

by:patrickab
ID: 33532531
-Polak,

Please upload your file - make the employees and data anonymous first!

Patrick
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33532717
You could also upload a simple sample of the file, that is, with just half a dozen or so fictional employees and how they relate to the other sheet.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33533003
Here you go.
EDforEE.XLSX
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33533176
That is really complicated. I don't think I can understand that without an explanation of each sheet.
Anyway, it seems to me that you will need VBA to automate it. You can't do it with Excel, cause you're populating automatically from the first sheet. You actually add data to other sheets.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33533338
"OCW260" is the data driver.
"Totals" is irrelevant.
"EO, OR&S, BO, EMO, MSF, FOF, AMF, BPFTI" are the various depts. and the specific personnel in them.
"EO Vacant, OR&S Vacant, BO Vacant.... etc" are the vacancies in each dept.
"AC Funded" is irrelevant
"IPA" is irrelevant

I suppose i could just create a macro that deletes all rows that contain "#REF!" when you press Ctrl+X, but I was really looking for a better way (especially for the if creating an employee situation).
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33533397
You could use Worksheet_Change even to monitor any changes, including adding an employee.
The biggest difficulty, from what I gather, is that, when you hire a new employee, you manually add it to the respective department, then paint down the rest of the cells. There really isn't an automatic check that tries to match a new line in OCW260 with the department.
So, VBA would be your biggest friend. Seeing as the things you're trying to do are mostly simple, you can probably do it on your own. If you have any difficulty, use the macro recorder to replicate an action and see how VBA does it.
0
 
LVL 11

Expert Comment

by:Runrigger
ID: 33533400
Polak,

A couple of questions;

1) Can an employee be on more than one sheet?
2) Which column on the master sheet will decided which individual sheet(s) the employee would appear on?

If the adding to a sheet can not be logically derived from the master sheet, you would need to add a mechanism for selecting a target sheet(s), say a pop up list on an Excel form.

This is not a straightforward task, not impossible for the experts however
0
 
LVL 45

Expert Comment

by:patrickab
ID: 33533519
-Polak,

That is hugely complex and would require a lot of detailed work to track down every cell reference for a specific employee. I believe that due to the complexity it would be best for you to record a macro and then see whether that can be made generic.

Patrick
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33533571
It probably can. All he needs to do is:
If the event is a deleted row, hunt down any #REFs and delete that row. If it's possible (not really sure about this), read the department and store the appropriate sheets in a variable, so all you need to do is select the correct sheet and delete the appropriate records.
If the event is a new row, then store the appropriate sheets in a variable, and then do the same steps for it. (I've noticed that each sheet is similar to the other departments).
0
 
LVL 1

Author Comment

by:-Polak
ID: 33533577
Cluskitt you lost me at VBA.

Runrigger:
1) No; however, when someone clears a BI or EODs it is necessary to manually move him from a "deptx Vacant" sheet to the regular "deptx" sheet.... (this is less of a big deal)

2) Two collums technically dictate that; the "Department" (F) to indicates which department sheet they should be on, and "Work Location" indicates if they are a vacancy.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33533628
In VBA, there is an event called Worksheet_Change. Every time a cell is changed, that event is called. You can then run any kind of code. In this case, and seeing as there are only 2 cases that interest you, you can add a check to see whether a row was deleted or inserted. Depending on which it was, you can run a set of actions and it would be done every time a new row was deleted or added.
If you have difficulty coding it, you can use the macro recorder, then simulate those situations:
-Do all the actions you would do if a row was deleted and check the code, so you can adapt it to your needs.
-Do the same for a new row.

If you need help with that, you can post that automated code, and we can adapt it to your needs.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33535306
To preface, I've never even opened VBA, but am willing to give it a shot... unless anyone has a better suggestion? (runrigger?)
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33535430
Macro recorder is in the toolbar. Open it, set it to record, give it a name and then do all the actions you normally do when inserting an employee. When you're done, go to the same place and stop recording. Then start recording again, give it another name and do all the actions you normally do when deleting an employee.
After that, hit ALT+F11. In the left panel, you'll see your workbook and all the sheets. At the end, there should be a "+ Modules". Expand it, double click on the Module1 that is there. You can now see all the code you created. Copy it and paste it here. We'll have a look and assist you in getting things to work.

One advice, though: create a backup copy of the file, and from now on work only on that copy until you're sure everything is working 100%.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33536640
Delete an Employee:
Sub DeleteEmployee()
'
' DeleteEmployee Macro
' Delete "#REF!" Linked Rows
'
' Keyboard Shortcut: Ctrl+Shift+A
'
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Selection.Find(What:="#REF", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Sheets("EO").Select
    Cells.Find(What:="#REF", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
End Sub
0
 
LVL 1

Author Comment

by:-Polak
ID: 33536709
Create and Employee:
Sub CreateEmployee()
'
' CreateEmployee Macro
' Run Macro when creating an employee
'
' Keyboard Shortcut: Ctrl+Shift+S
'
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveCell.FormulaR1C1 = "John Smith"
    Rows("8:8").Select
    Range("B8").Activate
    ActiveCell.FormulaR1C1 = "Jane Smith"
    Rows("8:8").Select
    Range("C8").Activate
    ActiveCell.FormulaR1C1 = "Program Manager"
    Rows("8:8").Select
    Range("D8").Activate
    ActiveCell.FormulaR1C1 = "GS-340-14"
    Rows("8:8").Select
    Range("E8").Activate
    ActiveCell.FormulaR1C1 = "15"
    Rows("8:8").Select
    Range("F8").Activate
    ActiveCell.FormulaR1C1 = "Executive Office"
    Range("H8").Select
    ActiveCell.FormulaR1C1 = "DC"
    Range("I8").Select
    ActiveCell.FormulaR1C1 = "3556"
    Range("J8").Select
    ActiveCell.FormulaR1C1 = "8- Non Supervisory"
    Range("K7").Select
    Selection.Copy
    Range("K8").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Sheets("EO").Select
    Rows("7:7").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("A6").Select
    Selection.AutoFill Destination:=Range("A6:A7"), Type:=xlFillDefault
    Range("A6:A7").Select
    Selection.AutoFill Destination:=Range("A6:K7"), Type:=xlFillDefault
    Range("A6:K7").Select
    Range("A7").Select
End Sub
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33549584
Sorry I haven't got back to you yet. I haven't had much time. But from what I see, with the sample you've shown me and the sample file, it should be pretty easy to code something :)
0
 
LVL 1

Author Comment

by:-Polak
ID: 33559633
Take your time, (i think) the only difficult step will be creating several If-Then statements which point which worksheet an employee is on based on their Department.
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 
LVL 18

Expert Comment

by:Cluskitt
ID: 33559708
That shouldn't be that complicated, really. It would be more complicated to wait for enough changes to have all data to fill in, but a simple count would do that. It should be pretty simple, and other experts could probably work it out as well. I will probably only have time to start working on it on wednesday.
0
 
LVL 18

Accepted Solution

by:
Cluskitt earned 500 total points
ID: 33609837
Sorry once again for all the delay. Last week was busier than I thought. Anyway, I made a couple macros for you. These should work with a keyboard shortcut (I saw above that you already know how to define them). Basically, when you want to delete, you select a cell in the row (any cell) you want to delete, then run the delete macro. When you want to create, after you inserted all the values (or, at the very least, the department) you run the macro. Again, one of the cells in the row must be selected. This also assumes that when you had an employee to a department, you always add a line at the end, that is, you won't insert an employee for EO in the middle of the other EO employees, but always as last.

Sub InsertEmployee()
    Dim strSheet As String
    Select Case Cells(ActiveCell.Row, 6).Value
        Case "Executive Office"
            strSheet = "EO"
        Case "Another Office"   'just add all cases here. You replaced the values, so I can't add them all myself
            strSheet = "AO"
    End Select
    Sheets(strSheet).Activate
    Range("A1").End(xlDown).EntireRow.AutoFill Destination:=Rows(Range("A1").End(xlDown).Row & ":" & Range("A1").End(xlDown).Row + 1), Type:=xlFillDefault
End Sub

Sub DeleteEmployee()
    Dim strSheet, strName As String
    Dim iRow1, iRow2 As Integer
    iRow1 = ActiveCell.Row
    Select Case Cells(iRow1, 6).Value
        Case "Executive Office"
            strSheet = "EO"
        Case "Another Office"   'just add all cases here. You replaced the values, so I can't add them all myself
            strSheet = "AO"
    End Select
    strName = Cells(iRow1, 1).Value
    Sheets(strSheet).Activate
    Range("A2:A" & Range("A1").End(xlDown).Row).Select
    Selection.Find(What:=strName, After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    iRow2 = ActiveCell.Row
    Sheets("OCW260").Activate
    Cells(iRow1, 1).EntireRow.Delete
    Sheets(strSheet).Activate
    Cells(iRow2, 1).EntireRow.Delete shift:=xlUp
End Sub

In the end, however, you should consider changing the format you have now. It's very impractical. At the very least, you should have a few IFs, so you don't really need to be adding autofills.
Would be much easier to simply move it all to access, though. Then you could fill in the data by queries. Any new or removed employee, and all you needed to do was refresh.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33618563
The problem with creating it in Access is that there is a Visio Org. Chart currently linked to the excel sheet. I realize that you can link Visio to an Access DB; however, for ease of administration to the users of the database (becuase not everyone is familiar with Access) it was decieded to keep it in Excel.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33618684
You don't really need to link Visio with Access. You can still link Visio with Excel, you just link Excel with Access first. That is, you create queries in the same place where your info is now. For example, you could, on the first sheet, have: select * from employees
Then, on the third sheet: select * from employees where department="EO"
etc...
Once you fire/hire someone, all you need to do is refresh the corresponding queries and all data is updated and shifted.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33618722
Hummm I'll look into that, then start the process of assigning you lots of questions/points.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33618765
LOL. Sure, I can help you with that, if you want. You should open another question for the access "migration", but if you want to keep asking here, it's fine by me.
0
 
LVL 1

Author Comment

by:-Polak
ID: 33618969
Looking at your Macro's right now.
There seems to be a problem with the InsertEmployee:
If you insert the employee at the end of a "Departments" group of employees, it works fine. However, if you insert the employee in the middle of the pack then when you run the macro you get lots of "0"s for the reference. Since, employees are added in order of who they report to is there a way around this?
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33618999
Probably. I'd have to look into it. I just autofilled from the last line. But, as long as you don't add it at the top, it should be pretty simple to autofill from the first row to the last. I'll look into it as soon as I can (meaning, as soon as I can figure out how to construct this monstrous query I'm building :P).
0
 
LVL 1

Author Comment

by:-Polak
ID: 33619036
Just re-read your instructions it seems you already knew that is there a way around this?
0
 
LVL 1

Author Comment

by:-Polak
ID: 33619099
DeleteEmployee works just fine :)
0
 
LVL 18

Assisted Solution

by:Cluskitt
Cluskitt earned 500 total points
ID: 33619364
Yes, I knew that if you added an employee in the middle that it wouldn't work. And yes, there is an easy workaround, but I thought the other might do.

Anyway, the fix is easy, now that I looked at it. Just replace the last line with:
Range("A2").EntireRow.AutoFill Destination:=Rows("2:" & Range("A2").End(xlDown).Row + 1), Type:=xlFillDefault
0
 
LVL 1

Author Comment

by:-Polak
ID: 33619463
Works just fine! However it paints the spaces inbetween certain employees but those blank rows will be deleted once I have a complete database.

Thanks for the nifty Macros!
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33619518
Yes, the way I fixed it is, when you add a new employee, it basically selects the second row and paints all the way to the bottom (that is, the department employees+1, which is the new hire).

Mind you, this is just one way of doing it. You could do it with excel formulas as well. Something like:
=IF('OCW260'!F2="Executive Office",'OCW260'!A2,"")

Basically, replace the formulas you have with these. Remember to respect the first number, though, as that will be the starting row of the department, as you most likely well know :)
0
 
LVL 1

Author Comment

by:-Polak
ID: 33621953
Is there a way to insert a rule into the InsertEmployee Macro, that basically says IF "WorkLocation" is equal to anything that has the word "Vacant" in it then do not paint?
0
 
LVL 1

Author Comment

by:-Polak
ID: 33622429
Actuallly I've notice another small issue with the Delete Macro.

I've attached a new excel file for your review (in case i screwed somethign up, had to modify the code a bit to account for a new collumn "IPN") but don't think that explains whats happening.

Basically, when you insert an employee and run the ctrl+shift+I macro (I go ahead and delete the vacancy it copies over, see my previous comment) Then if I run the ctrl+shift+D macro to delete that new employee it also deletes the last line on the corresponding Departments sheet.

Why is that?
EDforEE2.xlsm
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33624918
For the worklocation part, I'm not sure I understand what you want. If the worklocation is vacant, isn't it easier not to run the macro?
Anyway, if you do want to add the check (possibly in case you don't notice it's vacant), just add:

Sub InsertEmployee()
If Left(Cells(ActiveCell.Row, 9).Value, 6)<>"Vacant" Then
    Dim strSheet As String
...
...
End If
End Sub

As for the line, yes, that is a consequence of painting on insert and deleting row on delete. To fix it, you can add this to the delete macro:

    Cells(iRow2, 1).EntireRow.Delete shift:=xlUp
    Cells(Range("B1").End(xlDown).Row, 1).EntireRow.Insert
End Sub
0
 
LVL 1

Author Comment

by:-Polak
ID: 33632289
I'm saying that while the macro is painting an entire Department, could it basically not paint the row that has an instance of the word vacant in the WorkLocation column, that way I don't have to go back and delete the vacancies from the individual worksheets as they belong on the "Xdepts Vacant" worksheet.
0
 
LVL 18

Expert Comment

by:Cluskitt
ID: 33634691
Ah, I see. that is more complicated. I guess it could paint them all, then delete the vacants. Something like:

Sub InsertEmployee()
    Dim strSheet As String
    Select Case Cells(ActiveCell.Row, 7).Value
        Case "Executive Director FM&E"
            strSheet = "EO"
        Case "Executive Office"
            strSheet = "EO"
        Case "Organizational Resources & Support"
            strSheet = "OR&S"
        Case "Budget Office"
            strSheet = "BO"
        Case "Enterprise Management Office"
            strSheet = "EMO"
        Case "Mission Support Facilities"
            strSheet = "MSF"
        Case "Air & Marine Facilities"
            strSheet = "AMF"
        Case "Office of Border Patrol Facilities"
            strSheet = "BPFTI"
        Case "Office of Facilities Operations"
            strSheet = "FOF"
    End Select
    intDepRows = Cells.Find(What:=Cells(ActiveCell.Row, 7), After:=[A1], SearchDirection:=xlPrevious).Row - _
        Cells.Find(What:=Cells(ActiveCell.Row, 7), After:=[A1], SearchDirection:=xlNext).Row + 1
    Sheets(strSheet).Activate
    MaxRow = intDepRows + 1
    Rows("3:" & Range("B65336").End(xlUp).Row).Delete
    Range("A2").EntireRow.AutoFill Destination:=Rows("2:" & MaxRow), Type:=xlFillDefault
    For i = 2 To MaxRow
        If i > MaxRow Then
            Exit For
        ElseIf Left(Cells(i, 9).Value, 6) = "Vacant" Then
            Cells(i, 10).EntireRow.Delete
            i = i - 1
            MaxRow = MaxRow - 1
        End If
    Next
    Cells(MaxRow + 3, 2).Value = "Total:"
    Cells(MaxRow + 3, 3).Formula = "=COUNTA(B2:B" & MaxRow & ")"
    Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Interior.ColorIndex = 37
    Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeBottom).LineStyle = xlContinuous
    Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeLeft).LineStyle = xlContinuous
    Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeRight).LineStyle = xlContinuous
    Range("B" & MaxRow + 3 & ":C" & MaxRow + 3).Borders(xlEdgeTop).LineStyle = xlContinuous
End Sub

What I did was:
1-Count the number of employees in the department (find the last row, find the first row, subtract them). This is so we know exactly how many rows we need to paint.
2-Delete everything from row 3 to the end (including the total)
3-Paint from row 2 to the last employee
4-Delete all vacants
5-Create Total 3 rows below the last employee

If you need to change the number of rows for the total, just change MaxRow+3 to MaxRow+5 or whatever. If you want a variable number of rows, you can "Dim intTotalRow As Integer" after the strSheet dim, then assign a value to it per department on the select case (same place you assign strSheet), then change MaxRow+3 to MaxRow+intTotalRow.

Anyway, hope this covers what you want. I had to make sure I delete the total as well, else it could be painted over on departments with lots of vacants, and it could be duplicated in departments with no vacants.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now