Validate Checkbox if several criteria in line is true

Mitja Rudel

Hi all,

I Have two sheets for monitoring and performing roundtrips.

First one is "monthly plan" and second table "results" gathers data via form.

I am trying to automatically validate checkbox in "monthly plan" if roundtrip was executed by planned person in current month.

To validate I should use criteria from table "monthly plan": Object, date, assigned to and crosscheck this with "Results" table: object, created date, created by

"Results" table will have roundtrips results for whole year ca 500 lines, where couple of fields could be doubled (object, assigned to/created by,)

I have tested several options (vlookup, collect, index...) but none returned result.

Hepl would be appreciated

GrahamWelsby

Hi Mitja,

Just so I can understand, you are assigning tasks in the 'Monthly' to an individual 'person', then someone is recording a result in the 'Results' sheet - this may be filled in by another individual, and you are trying to see if the correct person completed the task?

Do you have unique identifiers that would be the same on each sheets - such as a project number?

Thanks

Graham

Mitja Rudel

Dear Graham,

Initially I was trying to use only one table and use "Update request" but later didn't find correct rules or logic which fields (cells) are needed to fill out. So I separated into two tables.

"Monthly plan" is used only to assign a specific object/building to inspect to specific person and has a "completed" checkbox.

This specific person should fill out "Results" table via form which has logic behind (different object requires different inspections=different coloumn in one row to fill). "Results" table have auto field to record who entered the data and when.

I am looking for a solution how to check from "Results" table if specific person (from "monthly plan" completed inspection in that object within planned month (not exact date). If yes, checkbox true, else false.

GrahamWelsby

OK, so are you using a unique identifier (auto generated when logged/or entered from another system) number for your Monthly plan ID?

If you are, the logic in your 'Results' form would have the provision of typing that in? Then from this number you can run Vlookup's or Index/Match to pull the relative information from the Month Planner sheet. there isn't a way of logging the person (to my knowledge) automatically, you may need to add a contact list drop down for who's logging the results, or who was it who did the inspection.

Regarding the completed in the same month - you can use an auto filled column with Auto Numbered Column type of Created date - this way when filling out the results form it's the actual date. Pull in the planned month from monthly planner (as above but the date column). Then utilise Month() function and possibly Year() in an IF statement e.g.

=IF(MONTH([Column2]@row) = MONTH([Column3]@row), "COMPLETED", "LATE")

Obviously change the text results to something more suited to your requirements.

Mitja Rudel

Hi,

In "Monthly Plan" there is no auto identifier and users can not see auto generated identifier for"results" table

In "Results" table I am using uniqe indentifier which is assigned by the SS. All users use SS mobile app or desktop SS and are required to log in so I have "created by" in "results" table also registered automatically.

IF is clear but in addition I would need to also check if user and object is the same in "Monthly plan" and in "Results"

GrahamWelsby

Hi @Mitja Rudel,

if you don't have a common field, how would you reference the same logged row in the Monthly and Results? Do you have a common number in both sheets to search for the details you need?

OK, created by, are you assigning with the same format in the Monthly Plan? if you are, then a simple IF(AND.... function could solve the user check.

Mitja Rudel

Dear Graham,

This is why a multiple check is neccesarry...

"Created by" is the same, but in addition I would also need to check month of execution and object since table will have multiple inputs from same users.

GrahamWelsby

Thanks @Mitja Rudel, without a common identifier I'm not sure how you would pull this through.

For example, if you had a Monthly Planner Ticket No, that they need to fill in on the 'Results' Form, then you can use either a Vlookup or Index/Match to pull in the cells that you require.

From there, you can add further columns to run your checks.

As above, the month can be checked...

=IF(MONTH([Column2]@row) = MONTH([Column3]@row), "COMPLETED", "LATE").

You could run another column check for the person such as...

=IF([UserAssigned]@row) = [UserCompleted]@row), "Correct User", "Different User")

but without some sort of unique identifier, I'm not sure you can pull these through - Unless there is only one job per item and is unique in it's self.

Mitja Rudel

Hi Graham,

I needed to reflect and try to start with a different concept but I am stuck. Could be entirely wrong approach from so I am open to sugestions.

The idea is, to have a monthly plan and to perform building inspection. I have 20 buildings and ca 10 different forms to fill out. Some fields are of course the same but several are different.

I used conditional logic in 1 form to cover all different inputs, but I could also prepare several forms depending on object type. Mybe in that case I could use only one table including plan and request update on that line. The challenge is how to offer different form input for different building...