Check Box Across Sheets

hjarriaran

Hi,
I have two sheets, one is an intake sheet and the other is a metrics sheet.

Metrics Sheet

Plan has an OR(ISDATE( Formula to raise flag is a date is entered

Agents has an IFERROR(INDEX(DISTINCT formula to capture the agents from the Intake Sheet

Intake Sheet

I want to add a Formula that can Raise a Flag under the PLAN for the Intake sheet based on a flag being raised on the Metrics Plan sheet and based on the Employee Name/Agents.

The Goal:

Basically, this is an occurrence tracker, It will collect events up to a year, and after 365 days, the row gets moved to a trash sheet. However, if the Employee meets a certain criteria of too many occurrences, they get placed on a plan. At that point I want the sheet to stop removing Dates for that employee even after the year is up.

The only way I can think of achieving that is by getting the plan on the metrics to align with the plan column of the entries for each person.

hjarriaran

I figured it out,

I ended up doing a IF(COUNTIFS Formula and it worked!

Adam Murphy

From the metrics sheet add to your formula with something like IF({Plan} = 1, 1…note that a checked checkbox is equal to 1, unchecked is 0. Will that work?

hjarriaran

I tried this before and always got the Invalid operation.

What I really would love, is something like this:

=IF(AND(HAS({Agents}, [Employee Name]@row), {Plan} = "True"), 1, 0)

hjarriaran

I figured it out,

I ended up doing a IF(COUNTIFS Formula and it worked!