Automatically preserving historical data

[Guest]

I have hit a wall trying to figure out the most effiecient way to automate
the recording of historical data (interest rates in this case). Say I have
15 different lenders, each of which has a different interest rate, and each
rate changes at a unique time, and under unique conditions. I have each
lender set up on its own sheet to calculate the current rate, no problem
there. But I am trying to build a single "historical rates' page that will
list each lender across the top, and each day down column A. How can I best
automate the process of recording each days interest rate in the historical
rates table (vs having to manually update the table whenever the file is
opened). I keep running into issues revolving around the various dates
associated with each lender.

Thanks in advance

 

[Guest]

A bit more info on the approach I am trying: Each cell on the historical
table has the current rate in it right now for all future dates. If there is
a way to do a conditional formatting where the cell data would be locked, and
not change as the current rate changes, based on the date being earlier than
"today", that would do it. Is this sort of conditional formatting available,
or is there a formula that would effectively lock the rate once the date
passes?