I am trying to automate a rent increase on a certain date. I have a table with Tenant ID, Rent Increase Date, New Rent amount. I would like to reference this in another sheet, so that when the rent increase date arrives, it will return the new rent amount. 

I can use the Index and Match to pull in the amount, just needing it to update on a certain date is what is giving me trouble. 

Any point in the right direction would be appreciated. 



Try working in something along the lines of...


TODAY() >= [Rent Increase Date]


Without seeing your setup, I can't give more specific advice. If you'd like to share more detail and possibly some screenshots, I'd be happy to help figure out how to best work it in.

Thank you for your help.

This is table I have built. One property may have an increase every 5 years and others may have only have 1 the entire term of the lease. 

My destination sheet could house the increase date column or this table could. Whichever is easier. I would like for a Current Rent cell to update based on the date and amount from the table, if there is on, otherwise stay the same. 

Hope this explains, I may be trying to do too many things at once.

Ok. This shouldn't be too complicated. Can you post a screenshot of the other sheet as well along with any formulas you may be using already?

In reply to by Paul Newcome

I had it semi automated, and changed it manually after an increase occurred to forward it to the next increase...( see first screenshot and listed dates and amounts. Then I thought what if I created a table with them all listed out and the formula could just pull them on the said date. Then I would not have to manually change the information after initial input. 

My index and match works, but just didn't know best way to leverage date. 

(I have dummied the data, to protect tenants, but the concept is there)


thank you