I'm trying to get a spreadsheet working in Google Sheets and I'm having a hard time finding a formula that will fit my needs, so I'm hoping someone here can help.
| Column A | Column B | Column C | Column D |
|---|---|---|---|
| Cell 1 | Cell 2 | Cell 3 | Cell 4 |
| Cell 5 | Cell 6 | Cell 7 | Cell 8 |
To be specific, I need Cell 2 to check Cell 1 to see if its empty. If 1 is empty, 2 can stay empty. Once 1 has input, 2 needs to return a specific time.
While Cell 3 should check Column 4 to see if its empty. If 4 is Empty, 3 can stay empty. Once 4 has input, 3 needs to return a specific time.
ALSO, if BOTH 1 & 4 have input, then BOTH 2 & 3 need to stay empty.
I also need it to be repeatable down a table of cells, which is why I've included a second row.
I have gotten the first part working using =IF(A1<>"", "8:00", "") which works for both Cell 2 and Cell 3 to check the outer cells, but I can't find a way to get the second part to compound and check if both cells have input.
Thanks in advance!
CodePudding user response：
Something like this? For B2:
=IF(A2="","",IF(D2="","8:00",""))
For C2:
=IF(D2="","",IF(A2="","16:00",""))
You can set them as array formulas, you put them in B2 and C2 respectively and they'll drag automatically to the end.
=ARRAYFORMULA(IF(A2:A="","",IF(D2:D="","8:00","")))
=ARRAYFORMULA(IF(D2:D="","",IF(A2:A="","16:00","")))