If(AND Statement between 2 Sheets

Katy Miller

I have a form that is filled out by a handful of managers through our organization. I am working on creating a roll up to confirm that the form is completed by each person each month, and link it to an automation. I was trying to do a if and statement, then build automation based on that.

For Reference {Manager Form} is a spreadsheet and {Manager Tracker} is a spreadsheet.

{Manager Form} has a column for Manager Name and a column for the month/year of report, as well as many other lines for other items on the form. This is a big spreadsheet and will be completed monthly by multiple managers.

{Manager Tracker} is going to assist in tracking if each month of the year has a response. So I was planning to have it set up to check if the manager has submitted their monthly report by a specific date then automate a message for the DM to complete it if not completed by a specific date.

(Automation I am not having an issue with, it's the if formula reference that is referencing the other sheet that I am having an issue with.

On {Manager Tracker} I input the following statement to see if the March form was completed by a specific manager:

=IF(AND(({Manager Form Range 1}) = "March 2021", ({Manager Form Range 2}) = "Manager Name"), "Complete", "Not Complete")

This if and statement is returning an invalid operation error.

Can someone help me understand how to fix the issue.

Thank you!

KM

Mark Cronk

Hi @Katy Miller ,

I suspect your issue is in how you set this up. Can you attach screenshots with the column names for both sheets? Which columns are included in your Manager Form Range 1 and 2?

Mark

Katy Miller

So here is a portion of the spreadsheet that is attached to the form that is being filled out monthly.

Here the draft of the tracker I am working on.

I plan to have a different column on the tracker for each month through 2021. So where it shows invalid operation I have the following statement:

=IF(AND(({Manager Form Range 1}) = "April 2021", ({Manager Form Range 2}) = "David XXXXXX"), "Complete", "Not Complete")

Which is referencing the form spreadsheet for the Manager Name column (range 1) and Scoring month column (range 2) from the Manager Form Spreadsheet.

We were formally using the Status column on the tracker and manually updating Complete or incomplete, but want to set it up so it's automatic showing Complete or incomplete for the month.

I can try to get better screenshots in the morning if needed.

Thanks.

Katy Miller

I ended up finding another way to do this using the pivot table application.

Thank you,

Katy Miller

Mark Cronk

Excellent. Glad you found a solution. Thank you for contributing to the Community.

Mark