IF for multiple columns across workbook

**craigtone** · 07-31-2012, 03:00 PM

I am creating an engineering project checksheet and need to put a status flag on the front page. The status flag will display either "NG" or "OK" with Conditional Formatting to color code red or green respectively.

The conditions that need to be met (sorry for my non-Excel formula ... just trying to explain):

IF (Sheet:'Place Review':Column F="Open" AND Sheet:'Place Review':Column G="N" AND Sheet:'Route Review':Column F="Open" AND Sheet:'ROUTE Review':Column G="N") THEN "NG" ELSE "OK"

Basically, I want it to search these 4 columns for either an "Open" or "N" and if it exists, set the flag to NG. FWIW, those columns are controlled by a list so the data can only be Open/Closed or Y/N. The number of rows in these columns is open-ended as these 2 sheets are an open issues list that could contain anywhere from zero to ? rows of issues.

Make sense? If so, PLEASE HELP ME!!!

**Pete_UK** · 07-31-2012, 03:29 PM

So, if you had this formula on row 10 of your front page, for example, would the cells that we are checking also be on the same row in those other sheets, or could they be anywhere? If anywhere, then what other item of data is there that we can use to find the corresponding row(s) on the other sheets - some unique ID or project number perhaps?

Pete

**craigtone** · 07-31-2012, 03:32 PM

See attached. I want the formula on M2 of the Summary page and you'll see the other columns I am referring to in the workbook. I am not looking to do this on a cell-to-cell basis, but rather just one status cell if ANY of the cells in those 4 columns contain "Open" or "N".

**Pete_UK** · 07-31-2012, 03:51 PM

Okay, you could probably do it like this:

=IF(AND(COUNTIF('Place Review'!F:F,"Open"),COUNTIF('Place Review'!G:G,"N"),COUNTIF('Route Review'!F:F,"Open"),COUNTIF('Route Review'!G:G,"N")),"NG","OK")

Very similar to how you had written it !!

Note: COUNTIF returns a number, and so I could have written >0 after each COUNTIF expression, but in this case that is implied as every value not equal to zero would be treated as TRUE.

Hope this helps.

Pete