I have two worksheets, one has data, and the other has a list of the table column names and a checkbox to tell whether the column is mandatory or not. On the far left of the spreadsheet, there is a status column that displays valid for a "Valid" row or "Invalid" for an invalid row.
I want the mandatory column to drive the status field in the Data Worksheet.
For each mandatory column, there must be data in the corresponding row. If all mandatory columns have data in them, then change the status for that row to "Valid"; if not, keep it "Invalid."
DATA WORKSHEET
| Status | Col1 | Col2 | Col3 | Col4 | Col5 |
|---|---|---|---|---|---|
| Valid | 1 | 2 | 3 | 4 | 5 |
| Valid | 1 | 4 | 5 | ||
| Invalid | 1 |
HELPER
| Column | Mandatory |
|---|---|
| Status | FALSE |
| Col1 | TRUE |
| Col2 | FALSE |
| Col3 | FALSE |
| Col4 | FALSE |
| Col5 | TRUE |