Forum Discussion
auto populate excel tab with training opportunities
Hi,
My manager has asked me to look into streamlining our excel document that is meant to track our mechanics competencies and also keep track of which manufacturers courses they have done and when the last time is that they have done them. not all mechanics work with all brands so i don't want to have the unnecessary brands on the mechanics tab.
my idea is that i would have a master data sheet that has every course our manufacturers have available linked to the manufacturers brand name.
but i would like to know if it would be possible that when i fill in those brand names on the specific mechanics excel tab as his competencies if it could then auto fill that mechanics tab with the manufacturers courses that are applicable to him.
and secondly it would be amazing if it is possible that it auto updates the mechanics tab if a new course gets added to the master data sheet linked to the manufacturer he/she has as his/her competency, so i do not have to manually update all the different mechanics tabs with the new course.
below a quick example of the courses tab and a simple mechanics tab to hopefully make it clearer. the courses would auto populate under the available courses.
6 Replies
It is quite possible to build a list of relevant courses dynamically. There is a catch though. If you expected to use the individual sheets to maintain the record of courses attended, you need to bear in mind that the manual input will not adjust as the list changes.
The changes you could make on the master sheet would be limited to appending a course; any deletions, insertions or re-sorting would create a mess.
that is indeed not something i considered as i would be inputting the date of the training manually
but also didn't think or deleting or re-sorting the trainings in the master sheet. i do want to be able to add new trainings to the master sheet that in a perfect situation would than be auto populated below the already shown courses in the mechanics sheet like in the example.
would something like the below example be possible where i would make seperate fields for the competencies and than it populates them to the right instead of as a long list down below. this would than not shift any manual input as that would stay on the same field if the new course would always just populate underneath the already existing shown courses. ? the only issue would then be that i cannot delete or re-sort anything in the master sheet without causing problems.
Would something like this work for you?
Using the data as validation lists separates the user data from the dynamic data
