I am preparing a financial forecast and want to have the ability to move rows of revenue data to an earlier or later start date. For example, I have a contract beginning Dec 05 with monthly revenues running until Nov 07. I want to be able to change the start date and have the same numbers move to the right or left in the spreadsheet depending on the new input date. A what if concept.
The at if function is too complex and each cell requires huge formulas. I have tried named ranges and I can cut and paste the whole range easily but then the links to other parts of the workbook don't work. If Dec 05 is linked in my base worksheet to say Dec 05 in the P&L, if the data is moved to start Jan 06, the link stays in Dec05.
I dont know if this is a macro/VBA issue or whether there is some other way. If someone could just tell me the excel functionality to explore that in itself would be helpful.
I am sure this is a standard need but all the books and the posts I have read never seem to deal with it. Wouls appreciate some guidance.