Forum Discussion

Soren's avatar
Soren
Copper Contributor
Sep 24, 2017
Solved

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 ...
Formulas & Functions
Need Help
  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    Sep 24, 2017

    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.

     

     

     

Soren's avatar
Soren
Copper Contributor
Sep 25, 2017

May I ask what does this part of the formula do?

 

IF(F14="occupied","occupied","vacant"

 

Thanks!

IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
Sep 25, 2017

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.

Resources