So...I'm hopeful I can explain my question adequately and find some solutions to the challenges I'm having with my latest spreadsheet project. In a nutshell, I have a handful of spreadsheets that I'm using to track and compare hundreds of stocks. One of those spreadsheets has a table of historical data with stock ticker symbols in the left most column and dates running in descending order along the top. There's only one price of each ticker symbol per date. I put the dates in descending order for several reasons, one of which is so that the most current recorded stock price is readily visible on the screen (please do not suggest putting the table in ascending order). I'm running several calculations on the prices in the table and I'd like to regularly update the table with the newest stock prices. This entails inserting a column before column D and pushing the existing prices and dates to the right of the table and pasting the new prices and date into column D. Here's the challenge. I'd like to make several calculations that are based on a particular time frame and continuously update the table with current prices while maintaining accuracy with the formulas. For example, one formula might find the minimum stock price of ticker symbol HK over the last year. Hypothetically, we might say the range of data for this formula is cells $D$5:$D$200. As the table is updated over time, the data is shifted to the right as more current prices are inserted in column D. So as I insert data into column D to include the most recent price of HK, the range has changed to $D$5:$D$201. The dates that correspond to that range are no longer capturing a year of data. It is capturing a year plus a day. The more I update my table, the less accurate the formulas become. I know I could re-write the formulas every time I update the table to precisely the dates I want to include; however this is too cumbersome. There are too many stocks and too many formulas and too many date ranges. Does someone know how to write a formula that allows me to insert a column in the range that's referenced by the formula and maintain absolute reference to the formulas in the table? In other words, I'd like to insert a column D to shift over past prices but maintain the reference to $D$5:$D$200. I've attached spreadsheet that's very watered down, compared to the real deal, but it will at least give a frame work of what I'm trying to describe.
