Formula Help

chelsea

I'm having trouble putting together a correct formula or workflow to do certain tasks. I have a rent tracker spreadsheet and want a formula to basically say "When it's 30 days prior to the Rent Increase Date then automatically turn the Status Column drop down to red. Once the Status Column drop down is changed to green then have the Current Rent Column change to the Rent Increase Column amount". 

ALSO

I have a separate Monthly Rent Pay Spreadsheet that I would like to cell link to the above referenced sheet. That part is easy to figure out. BUT is there a formula or something I can set up that says if any changes have occurred in the linked cells then highlight them a certain color? I want any changes to stand out because I make changes through out the month and put it all together at the end of the month. 

Paul Newcome

I believe what you are asking for is possible (maybe not the second part though).

Are you able to provide some screenshots with sensitive/confidential data blocked out, removed, or replaced with "dummy data"?

chelsea

I've attached dummy data with the corresponding columns i am referencing to. 

Paul Newcome

Ok. So for starters, we can use this in the Status column:

 

=IF([Rent Increase Date]@row <= TODAY(30), "No")

.

In the [Current Rent] column:

 

=IF(Status@row = "Yes", [Current Base Rent]@row + [Rent Increase Amount]@row)

.

I believe this is what you are looking for. However... Any manual changes to a cell within a formula will essentially overwrite/delete that formula.

.

As for the second part concerning the highlighting of cells...

 

You could either use conditional formatting to highlight based off of a Modified (date) column or a manually entered date column, or you could use the Highlight Changes feature as shown in the screenshot below.