Formula for If/And/Then Statement Referencing Other Sheets Including Dates

maxmpma

A little convoluted so hopefully I can explain this well. I have sheet (let's call it sheet A) that is used to input data from a form each month for performance updates on personnel. I'd like to be able to track each month which performance leads have or have not inputted that data. So in another sheet (let's call it sheet B), I have a list of the personnel, their leads, and I figured a column that just tracks each month as a to-do list for who has or has not entered their data.

In my mind, I'm trying to do the following formula on Sheet B: =IF(AND(CONTAINS(Personnel@row, {Sheet A Range 1}), {Sheet A Range 2}@row >= TODAY(-20)), "Submitted", "Needs to Submit")

The problem I seem to be having is grabbing the date in in the past 20 days from the other sheet, the rest of the formula if I break it out seems to work well. On Sheet B, the Personnel name is in the "Personnel" row so the first part of the formula simply says if you find in this column on Sheet A (Range 1), the personnel name from this row in Sheet B, then true. Then the second part of the formula I'm trying to say, "and if on that Sheet A, within those rows with that personnel name, if the Created Date column (Range 2) is equal to or less than 20 days from today in at least one instance, then true." Obviously "Submitted" being true and "Needs to Submit" being false. Since we always update these at most 14 days before our meeting, that 20 day metric works great because it's simply asking the question, was a row added in Sheet A for this personnel in the last 20 days?

Any idea where I am messing this up?

Brian_Richardson

Because you're using two criteria in an AND statement, they aren't playing "together" to give you just the results you want. You cannot use a cross sheet reference and tack @row on it to try to get just the matching row from the first criteria (the personnel)…in fact you cannot put @row on a cross sheet reference at all.

Try using a COUNTIFS instead which will give you the combined result:

Also, is the Personnel column on Sheet A a single select or multiple select? I'm guessing multiple since you're using CONTAINS. Instead, for multiple selection columns, use HAS.

= IF (COUNTIFS({Sheet A Range 1}, HAS(@cell, Personnel@row), {Sheet A Range 2}, >= TODAY(-20))>0, "Submitted", "Needs to Submit")