Forum Discussion
Trying to use conditional formatting to color the space between two data entries.
Here is an example of what I am trying to do,
https://docs.google.com/spreadsheets/d/1ejUJcvITfT31VSLi8d3SS1tyon1MP_6lCJUIRO629Js/edit#gid=0
I colored the cells just as an example. This would be a shared spreadsheet where rental home owners would have access to the sheet and would enter in the check in and check out dates for the guest, usually multiple time a month. Any suggestions or ideas ?
Thanks
Hello,
I suggest you use a helper column with this formula, starting in F3 and copied down:
=IF(ISNUMBER(B3),"occupied",IF(ISNUMBER(D2),"vacant",IF(F2="occupied","occupied","vacant")))
That will give you a column of values of "occupied" or "vacant". You can then use conditional formatting with a formula for columns B to E. (By the way, avoid merging cells like that. It can lead to problems).
The conditional formatting formula is
=$F3="occupied"
You can then hide column F, so it does not show in the sheet.
4 Replies
Hello,
I suggest you use a helper column with this formula, starting in F3 and copied down:
=IF(ISNUMBER(B3),"occupied",IF(ISNUMBER(D2),"vacant",IF(F2="occupied","occupied","vacant")))
That will give you a column of values of "occupied" or "vacant". You can then use conditional formatting with a formula for columns B to E. (By the way, avoid merging cells like that. It can lead to problems).
The conditional formatting formula is
=$F3="occupied"
You can then hide column F, so it does not show in the sheet.
May I ask what does this part of the formula do?
IF(F14="occupied","occupied","vacant"
Thanks!
Hi Soren,
this part IF(F2="occupied","occupied","vacant"))) checks if the cell above has the word "occupied" and if so, it repeats it. If an end of the occupation has been entered, the formula won't even get to that evaluation, because this IF(ISNUMBER(D2),"vacant", will return the "vacant" flag and then exit the formula.
- THANK YOU!!!
