Excel Tables Feeding Information

[ECregg]

I am attempting to create a training tracker with Excel, where one page holds the employee information (name, number, hiring date) and the other pages hold the position's trackers. The intent is to input an employee in the master page, and have that information sorted out to the tracker sheets automatically. I tried using SORT(FILTER()) but that only carries the basic information, and leaves the rest of the tracker floating so if the order changes a lot of information is left hanging. I attached some screen shots for reference. My thought is if I could insert this function into a table, it would all stay attached, however I get a SPILL error if I do that.

[#2]

@ECregg 

Images are not really all that helpful. Is it possible for you to post a copy of the actual spreadsheet--ideally with a few (false--use Disney or Star Wars characters) names, and dummy data--posting it on OneDrive or GoogleDrive with a link pasted here that grants edit access?

[#3]

@mathetes yes, here is a copy of the file template I created. Training Template.xlsx

[#4]

@ECregg 

OK, I have the file. Now, if you don't mind, a bit of help navigating it. I'm assuming that what you refer to as "the master page" is the sheet named "Employee Split", but what happens next. What gets tracked? Where?

Where is it that you're experiencing the SPILL error. Is that on the file you posted? I don't see it.

[#5]

Yes the employee's information would be entered into the Employee Split Page, which would sort them out to the other pages based on position. I removed the formulae I was using since they did not work for what I was trying to do. The =SORT(FILTER()) was pulling information from the employee split page, but when I entered information to the right of that, in the actual training tracker, it was no longer a table and so was not attached to the names I was entering. If I entered a new name, the training information would not adjust for the new rows.

[#6]

The formula I was using is =SORT(FILTER(TABLE1[[EMPLOYEE NAME]:[EMPLOYEE ID]], TABLE1[POSITION]="Position 1","")). With this formula, I had to include an extra table that included the position title again.

[#7]

@ECregg 

I'm suggesting here a different starting point. As a person who had been (before I retired 20 years ago) BOTH the director of the HR/Payroll database of a major corporation AND a person who spent most of his 29 year career leading management training programs, I have some experience with both aspects of your project. Both the database (or IT design) part of it, and the tracking of training. It does look as if you're doing more in the area of very specific training, and training that is required (perhaps for some licensing purposes)....but databases for whatever purpose have a lot in common.

So I want to begin with a single database for collecting employee data, just adding a column that differentiates by position; no need at all for a separate table for each position; in fact, that would get in the way over time, if not right off the bat.

It may be that what you need is one central database to track training too...and then just have various output reports that summarize for each employee or position or program who's been through it.

I'm NOT clear thought on what your next step will be here. What other input there is going to be, and then what out put. In designing a system, you want to articulate those two components, at the very least, and then devise the screens to accomplish the collection of info (INPUT) and those that will display useful information at the OUTPUT end. Then there's the matter of what processes take place to transform the input into useful output.

So could you describe a bit more of the data that needs to be collected here, and then what the output will be.

(A word of suggestion on the side: don't take time at this stage making your screens look pretty --colors, merged cells, etc -- when you haven't yet made it function as desired. Some of those formatting features can actually impede design, so going forward, as you get involved in other Excel projects, I recommend leaving the beautification to the very end; I like a nice looking sheet as much as anybody--many of my own workbooks make extensive use of color, conditionally changing colors even, but I always keep that until the whole workbook is functioning as I want it to.)

[#8]

Thank you for the advice about beautification, in this case I was recycling an old tool and just kept the formatting. The intent of the tracker is not for HR, but for the training department. Our site has a wide variety of positions and processes that the trainers need to keep track of. Many of these overlap, but not completely. The idea was to have a tool to keep an eye on each position, which processes they have been trained in, and the date each process was completed due to renewing certifications. Because of the number of employees we have, I thought to use separate sheets for each position for ease of use. You may be correct though that the sheet might perform better with everybody on one sheet and just use a report page for an overview.

[#9]

@ECregg 

My main point about my experience in HR is that I was director of. a database, in fact it was in the design of the HR/Payroll database. So I have experience with a highly normalized relational database. That's why I went to a single employee database, one that could track movement, hires, transfers, etc., from position to position (assuming that happens in your organization). It may well be that the training tracking, per se, deserves a separate table, with the link between them being the IDs of the people who go through the various trainings. Power Query could be used to join the "input" data, those separate but related tables of data, Power Query (which bears a lot of resemblance to SQL) could then be used to create your reports that track what training each employee has been through.

It sounds like you know how to use Excel.... so I'll leave it to you. If you do have questions by all means come and ask.