Need help creating a database that will automatically update.

[ava_h]

I'm currently working on a database that will contain employee information. I would like to be able to update the master sheet, but have corresponding sheets for each one of our six locations. That way if I put in a new employee and assign them to LOC 1 (on the master sheet), they will automatically show up on the sheet I've named LOC 1. I know I can use the autofilter to show the specific locations and the employees at that location on the master sheet, but it would stiIll be more convenient to have corresponding sheets for each location. I've researched several sites, but most suggest macros and I end up feeling bewildered.

 

[Derek Brown]

The solution given was for Excel 2007/2010 that makes it possible to use IFERROR.
Here are the revised formulas that will work for all versions of Excel:
Action 5:
=IF(ISERROR(MATCH($A$1,OFFSET('Master Sheet'!$A$2,0,KeyColumn-1,COUNTA('Master Sheet'!$A:$A),1),0)),"",MATCH($A$1,OFFSET('Master Sheet'!$A$2,0,KeyColumn-1,COUNTA('Master Sheet'!$A:$A),1),0))
Action 6:
=IF(ISERROR(MATCH($A$1,OFFSET('Master Sheet'!$A$2,A2,KeyColumn-1,COUNTA('Master Sheet'!$A:$A)-A2,1),0)+A2),"",MATCH($A$1,OFFSET('Master Sheet'!$A$2,A2,KeyColumn-1,COUNTA('Master Sheet'!$A:$A)-A2,1),0)+A2)
Action 8 (also removes the unwanted zeros):
=IF(ISERROR(IF(LEN(INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1))=0,"",INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1))),"",IF(LEN(INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1))=0,"",INDEX(OFFSET('Master Sheet'!$A$2,0,0,COUNTA('Master Sheet'!$A:$A)-1,COUNTA('Master Sheet'!$1:$1)),$A2,COLUMN()-1)))

 

[ava_h]

It works wonderfully! I definitely like the shorter formulas, but since not everyone on our system is up to date, I better stick with the others. Thank you again for all your incredible help!

 

[ava_h]

I have one more question. I thought about adding another spreadsheet for terminated employees. I really didn't want to change the region name because we'll need to know what store they were located at. Could I add another column and just put maybe "x" if they are terminated so that they would automatically be added to an "x" sheet? I would filter the other sheets to only show active employees. I assume this would somehow duplicate or override the initial formula?

 

[Derek Brown]

That should be quite easy to do.
My first thoughts are to change the region name, for example, to perhaps "Ex-POB" and this will then automatically remove them from the appropriate filter sheet (in this case the "POB" worksheet).
The "Ex-" could then be used to 'filter' the ex-employees to another worksheet.
So you could then either have them all on the one "ex-employee" worksheet or, if you prefer, have them on one worksheet for each region.
The latter is already there - just change the region to, for example, "Ex-POB" and create a new worksheet with the name "Ex-POB" etc.
If you want them all on one ex-employee worksheet, let me know and I will let you have the amended formula(s).

 

[ava_h]

I initially thought about just adding an "x" sheet, but then we would lose what region they belong to and since I don't have another column with this information, I would lose it. I would prefer to have them all on one sheet versus "ex-pob", but I still need a column to retain what region they were assigned to. I hope that makes sense .

 

[Derek Brown]

It can be done any way that you want, so that is not a problem.
Just to clarify what we need done:
Ex-employees to be on a single worksheet and still show the region (e.g as POB, POG etc.).
Now the choice/options:
Option 1.
On the MASTER sheet the region to show as, for example, "Ex-POB" to automatically remove them from the filter sheets. The "Ex-" would not appear in the region on the Ex-employees worksheet - the region would show as on the MASTER..
Option 2.
On the MASTER sheet have an extra column to show "x" for ex-employees. An extra column on the filtered worksheets would also show this. The "x" column would be used to identify the records for the ex-employees worksheet.
Option 3.
Almost like Option 2 but without the extra column on the filtered worksheets. The formulas would be changed to identify and ignore the "x" records.
I hope that I have explained this OK but please feel free to correct/add requirements etc.
Just let me know which you prefer.

 

[ava_h]

I think option 2. So it would be like adding a column that says "Status", then if someone puts an X in that column it would signify that person is a former employee and they would show up on an "X" sheet. My thought process is keep it simple so others can easily update the sheet without me having to explain it over and over.

 

[Derek Brown]

Here are the changes to pick up your "Status".
Make sure that you first make a back-up copy of your workbook before changing the one you are using.
1. On the "Control Sheet" add values to the following cells:
B1: Status Column
C1: Status Indicator
B2: 6
C2: x
2. Give cell B2 the name "StatusColumn" - remember, use the name box above the heading of Column A, press Enter (important) and no spaces.
3. Give cell C2 the name "StatusIndicator".
4. On your MASTER sheet add the "Status" column - if it is not column F, then change the value in B2 on the Control Sheet accordingly.
5. On all your other worksheets, also add the "Status" column and copy-across the formulas from the column before it - for example, if adding column F as the Status column, copy formulas from column E.
6. Create a new worksheet using one of the other 'filter' worksheets and rename it. For example, copy worksheet POB as POB(2) and then rename it to whatever you want (I called mine "Ex Employees").
7. Change the formula in cell A1 to:
=StatusIndicator
8.Now carefully change the formulas in column A. Remember that the formula in A3 downwards will be different to that in cell A2.
I suggest doing this with 'Find & Replace'.
You need to replace "KeyColumn" with "StatusColumn".
9. When happy that it is all working, hide column A.
That is it - if I have missed anything or anything is not clear, just update the post.

 

[ava_h]

So far it works perfectly! It was a lot easier that what I expected. I'm learning so much! I've never used a control sheet before, but I can see the importance of having it there. Thank you again!

 

[Derek Brown]

Thank you for the update - pleased that it is working OK for you.
Yes, having a 'control sheet' can be so useful - particularly when used with VBA - you can make some changes (e.g. workbook paths) using one cell without having to go into the code to make several changes.