Updating Training Status by Formula

I have a Training Schedule table with different Training topics (Topic A, Topic B, Topic C), assigned to Agents (let’s say agent Stacey).
I have a second table called Agent System Status which tracks the training of the agents. (Stacey has completed Topic A, and Topic B, and she is scheduled for Topic C).

When the training is complete, there is a box on the Training Schedule table that says ‘Training Complete’. When that is checked off, I want the Agent System Status for Topic C to update to ‘Completed’. I currently have a formula in the Topic C column which reads IF(FIND(“TOPIC C”,{Training Schedule}),“Completed”,""). This will show Completed as soon as it is assigned, but I want it to say ‘Scheduled’ when assigned, and ‘Completed’ when that box is checked off on the Training Schedule.

Is that possible? I cannot figure it out.

Welcome to the community, @Stacey_Hussey! :smiley: It sounds like you’ve got a formula in the primary field of [Training Schedule] to add the name of the topic, so that you can find it via your existing formula in [Agent System Status]. You could modify that formula to add " Complete" when the {Training Complete} box is checked. It might look something like this (using {Topic Name} to represent the field where the topic is chosen):

{Topic Name} & IF({Training Complete}, " Complete")

With that in place, you could modify your existing status formula as follows:

IF(
    FIND("TOPIC C",{Training Schedule}),
    IF(
        FIND("TOPIC C Complete", {Training Schedule}),
        "Completed",
        "Assigned"
    )
)