Hello! I'm having an issue that seems possibly beyond Excel's capabilities, but thought I'd check a few forums before giving up.
I'm creating a template to distribute to multiple people that asks them to enter a list of items and fill out several fields for each item. I'm trying to create a "flag" so that if they leave a column blank, an error message will pop up above the column. The problem I'm having is that these lists can be various lengths, and any of the rows could possibly have this error, but I need the warning to be triggered only by the rows being used for that person's template. I want the warning message to appear in one cell at the top of the sheet and would love to avoid having a separate error check beside every single row.
So to make an example, I've got a template that allows up to 5 entries, but they might not use all 5. In this case, the person only needs 3 of the 5 rows. I need to put a warning message in cell E1 if they leave an empty space somewhere in column E, but only for the rows with values in column B. Of course cell E6 in this case and any cells below it will be blank since there's only 3 items on the list, but some people might use all 5 rows, in which case I need to check all 5. Here, I'm only worried about the blank in cell E4. Is there a way to write an IF formula that reads all of the rows in the table, but is only triggered if column B of that row is non-blank and column E of that same row is blank? I feel like this will involve an array but I'm not sure.
Any suggestions would be super appreciated! Thanks!
-Claudia
I'm creating a template to distribute to multiple people that asks them to enter a list of items and fill out several fields for each item. I'm trying to create a "flag" so that if they leave a column blank, an error message will pop up above the column. The problem I'm having is that these lists can be various lengths, and any of the rows could possibly have this error, but I need the warning to be triggered only by the rows being used for that person's template. I want the warning message to appear in one cell at the top of the sheet and would love to avoid having a separate error check beside every single row.
So to make an example, I've got a template that allows up to 5 entries, but they might not use all 5. In this case, the person only needs 3 of the 5 rows. I need to put a warning message in cell E1 if they leave an empty space somewhere in column E, but only for the rows with values in column B. Of course cell E6 in this case and any cells below it will be blank since there's only 3 items on the list, but some people might use all 5 rows, in which case I need to check all 5. Here, I'm only worried about the blank in cell E4. Is there a way to write an IF formula that reads all of the rows in the table, but is only triggered if column B of that row is non-blank and column E of that same row is blank? I feel like this will involve an array but I'm not sure.
A | B | C | D | E | F | |
1 | [ERROR FLAG] | |||||
2 | TRANSACTION ID | COMPANY | COUNTRY | CATEGORY | ||
3 | 1 | ABC1234 | Company 1 | USA | Commission | |
4 | 2 | DEF1234 | Company 2 | UK | ||
5 | 3 | GHI1234 | Company 3 | USA | Fee | |
6 | 4 | |||||
7 | 5 |
Any suggestions would be super appreciated! Thanks!
-Claudia