My spreadsheet is about an employee vacation calendar. Each employee has 25 days of vacation per year, and he can suggest when it will occur, if the vacation will be split or not.
The data of the Sheet 'Responses' is directed to the Sheet 'Options'. Then, the Period chosen is split in Start Date and End Date in the Sheet 'Options Splited'.
The main formula, that starts at E4 in the Sheet 'Calendar', basically checks if the date cell (that goes from E$1 to NE$1) it's between the period chosen. If yes, it sets the value "VACATION' on that cell.
=IF((OR(and(E$1>='OPTIONS - PERIODS SPLITED'!$B5;E$1<='OPTIONS - PERIODS SPLITED'!$C5);and(E$1>='OPTIONS - PERIODS SPLITED'!$D5;E$1<='OPTIONS - PERIODS SPLITED'!$E5);and(E$1>='OPTIONS - PERIODS SPLITED'!$F5;E$1<='OPTIONS - PERIODS SPLITED'!$G5)));"VACATION"; "")
I dragged the formula from E4 to N41. However, I want to know if it is possible to extend the formula vertically to the line 41 and horizontally to the column NE, using the arrayformula function.
Note: The sheet below is a copy. I've reduced the original formulas to facilitate the comprehension. I hope it's understandable.
https://docs.google.com/spreadsheets/d/1Z9Pn9fV4Ptefd3v96m4LyoqUxxGrvUR07rn9zxEOsrQ/edit?usp=sharing
