Rolling over formula every month

[raj08536]

Hello Experts,

Every month, we have to copy existing months formula to next month (same formula) and convert to value.

For example right now we have formula in D18 (May) and value in Apr Cell C18. When we move to Jun I will copy the same formula to E18 and convert D18 as value (58.987).

Please suggest a solution. VBA, formula or anything. Appreciated.

 

[Saba Sabaratnam]

From your explanation and your formula, I believe that you are replacing balance sheet numbers for one month (e.g. April) with next month (e.g. May) and populating by summary sheet by linking it . if so, you could use this process:

Instead of replacing last month data with this month data, build a database with date as shown below. That is when you paste data for April, enter the date in column C for April and then copy May number below it with May date.

Once you have this database, you can use sumifs function to summary number for every month without replacing formulas with values. The sumif function in B2 (copy it down and across ) is:


=SUMIFS('Balance sheet'!$B:$B,'Balance sheet'!$A:$A,Sheet3!$A3,'Balance sheet'!$C:$C,B$2)

Kind regards

Saba