Create an automated Employee Database in Excel

-Polak,

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

Patrick

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.

Here you go.
EDforEE.XLSX

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.

"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).

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.

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

-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

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

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.

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.

To preface, I've never even opened VBA, but am willing to give it a shot... unless anyone has a better suggestion? (runrigger?)

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

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

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

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 :)

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.

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.

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.

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.

Hummm I'll look into that, then start the process of assigning you lots of questions/points.

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.

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?

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

Just re-read your instructions it seems you already knew that is there a way around this?

DeleteEmployee works just fine :)

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!

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 :)

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?

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

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

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.

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.