Automation for training sheet

[David Clark]

Good day all,

I am an officer with a Volunteer Fire Department and I am looking for some help building automation into my training files. I have an excel book with 50+ sheets for each active member of the department. This file is used to keep records of all mandatory training needed to remain an active riding member in the department. In the 2 images uploaded, one is the single sheet per member and the second is a full list of each member and trainings.

What I am trying to do is pull the data from the "Required Certifications" members sheet to the "testing" sheet and then it would place an X on the completed training once I enter anything into the information date section. I will then be using the "training" sheet to make a pivot table(no help needed with pivot table) that I can then send to the membership to show where we all stand at the 6 month mark of the year for completions.

If I can get a formula for one of the categories I believe I can change the needed formula to also pull from the others.

Thank you in advance and if this is too much of an ask or hard to do I can revert to how it used to be done by just adding the X myself like my predecessor did.

 

[Snakehips]

@David Clark Welcome.
David, for me this is crying out for a VBA solution.
Are you open to using VBA?

 

[Candyman8019]

You could try something like this in Testing Sheet B2.

=IF('Required Certifications'!G16<>"","X","")

If there's anything in the date field for that course then put an X, otherwise nothing.

 

[Snakehips]

@Candyman8019 it is my understanding that there will be 50 or so 'Required Certifications' type sheets. One for each Member.
I am assuming that each sheet will be named as per a Member? Not impossible to formulate using INDIRECT but perhaps far from ideal.
Hopefully David will confirm?

 

[Candyman8019]

My understanding is that there will be one 'Required Certifications' sheet per member...and the 'Testing Sheet' is a summary of those details. Considering each of the member's sheets will have an identical layout, this should be very easy to replicate the formulas once you have the first row sorted. A bit of work for sure...but easily doable with search/replace for sheet name rather than using any indirect formulas. I would avoid INDIRECT since it's a volatile function.

 

[David Clark]

Sorry got busy with other work and just getting back to this PC.

There will be one "required Certification" sheet per member, that sheet will be that members name, all sheets are identical. The "testing Sheet" is a summary for all of the members.

I dont know much about VBA.

 

[David Clark]

Thank you both very much, @Candyman8019 The formula you posted worked now to just change the sheet name to match and copy everything over.

Do either of you know how to copy the formula for column B down from B1 to B55 without the formula changing? The only way I can figure it out is to do a single copy, I am unable to drag, holding down CTRL while I copy doesnt work either.

 

[Candyman8019]

Try something like this:

=IF('Required Certifications'!$G$16<>"","X","")

Copy the cell, then highlight the range you want to copy it to and then paste

 

[Snakehips]

@David Clark I wish you well with this. Are you aware that if Members change and sheet names change you will need to revise formulas accordingly?
If you should wish to test my VBA solution then let me know.

 

[David Clark]

@Candyman8019 Thanks you I forgot I could add the $ to lock the letter/number.

@Snakehips Im always up for learning something new, Like I said though I have no knowledge of VBA, if that doesnt bother you Im in for the learning!