Question description:
This user has given permission to use the problem statement for this
blog.
Hello,
I'd like to create a training file where the first worksheet will have Training Topics for the first row, and the names of the technicians for the next 20 rows. Then I will create 20 worksheets for each technician.
Whenever I finish training, the topic will be entered for first row, and whoever in attendance will be checked off.
I would like a template where if I check off attendance for each technician, the Training Topic for that column will be copied to the technician's worksheet so that each technician will have individual record of all the training attended.
Thank you!
Solved by T. F. in 58 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
11/06/2018 - 02:26
Hello and welcome to Got it! :)
Excelchat Expert
11/06/2018 - 02:29
If I understand correctly, you wish to have a template in a way that you have an overall sheet and other sheets for each of your technicians. The overall sheet will contain training topics and will determine the attendance of the technicians for those topics, and you wish to copy the trainings attended per technician to their respective sheets. Is this correct? :)
User
11/06/2018 - 02:31
Yes, that's correct
Excelchat Expert
11/06/2018 - 02:31
Okay then, nice! If I understand correctly, you want the topics as column headers, is this correct? :)
User
11/06/2018 - 02:33
Yes, love the last topic btw! :D
Excelchat Expert
11/06/2018 - 02:33
Lol! Nice then, but I do hope no one applies that at work. Okay, let me work on it for a bit and update you the soonest! :)
User
11/06/2018 - 02:35
Thank you very much!
Excelchat Expert
11/06/2018 - 02:46
Okay, I'm a bit of having a problem here, since the details we're trying to fetch are in rows, not columns. It would be pretty easy if we have the trainings listed in a single column though, but I guess I can try to reverse this to work with a row fetching mechanism. :)
User
11/06/2018 - 02:47
Switching it will be fine too actually, as long as the info is captured then it's all good. Thanks!
Excelchat Expert
11/06/2018 - 02:48
Oh nice! That's good then. Okay, let me reverse the data then. Thanks so much! :)
Excelchat Expert
11/06/2018 - 02:54
Btw, will it be okay if we donate an "x" symbol meaning the technician attended the said training? :)
User
11/06/2018 - 02:56
Yeah, I've been using that actually, thank you!
Excelchat Expert
11/06/2018 - 02:56
Okay, nice! Thanks. :)
Excelchat Expert
11/06/2018 - 03:08
Already done! :)
Excelchat Expert
11/06/2018 - 03:08
Deepest apologies since it took this long. But I guarantee this will work. :)
Excelchat Expert
11/06/2018 - 03:08
Okay, so what we have are merely three sampled individuals.
User
11/06/2018 - 03:08
This looks great! Thanks so much!
Excelchat Expert
11/06/2018 - 03:09
however, what we will be doing is to replicate actually just one sheet of an individual and change its name
Excelchat Expert
11/06/2018 - 03:09
The name inside the sheet, not the sheet name btw. :)
Excelchat Expert
11/06/2018 - 03:10
So if we're gonna create one for Um Baba, we can just duplicate the sheet of superman, then change cell B1 (where we type in the technician's name) to Um Baba.
Excelchat Expert
11/06/2018 - 03:10
This will then be captured by the formulas under column A, and will match this on the overall sheet, and thus will return all trainings which Um Baba attended (those with x marks under his/her name)./
Excelchat Expert
11/06/2018 - 03:11
So just to note, we can add as many technicians and trainings that we like, and monitor all the way. We can just then create an individual sheet per technician as per the instructions above. :)
Excelchat Expert
11/06/2018 - 03:12
Just to note btw, the technician name on the overall sheet and on the individual sheet must exactly match. :)
Excelchat Expert
11/06/2018 - 03:12
Btw, you can download a copy of the document preview by going to File > Download as > Microsoft Excel (.xlsx). The formulas will be downloaded as well. :)
Excelchat Expert
11/06/2018 - 03:13
Do you have any questions btw? :)
User
11/06/2018 - 03:15
I just downloaded the file and going to try the instructions. Thanks again! That was really fun to watch! My excel is just taking a long time to load ...
Excelchat Expert
11/06/2018 - 03:18
Oh I see. I just edited some formulas to lessen the ranges a bit. (1000 technicians are a bit too much I believe). You may download this one and hopefully it'll respond faster. :)
User
11/06/2018 - 03:20
Oh thank you, my excel has just been quite slow. Sorry for this noob question but by "duplicate" the worksheet, is that the same as "move/copy" option? Then just change the Name?
Excelchat Expert
11/06/2018 - 03:20
Yes! So you just create a copy and change the name. :)
Excelchat Expert
11/06/2018 - 03:21
Oh, change the name of the worksheet, and change the name as well in cell B1. :)
Excelchat Expert
11/06/2018 - 03:22
So after you duplicate Superman sheet (and have another sheet named Superman (2)), you can just change it to, say, Um Baba. Then for the formula to truly take effect in that sheet, change the technician name in cell B1 to Um Baba as well. :)
Excelchat Expert
11/06/2018 - 03:23
Nice! If all is good now, please do end the session via the button on the upper right of your screen (sorry, can't do it from our end). Please do leave a good feedback upon your exit. Thanks so much and have a nice day! :)
User
11/06/2018 - 03:23
Yay it worked perfectly! Thank you!!
User
11/06/2018 - 03:24
Thanks again!! Have a great day!
Excelchat Expert
11/06/2018 - 03:24
You as well! :)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
