Freeze Reported Data in Previous Month

[phil152003]

I have to produce a report on a monthly basis that updates automatically from a Sharepoint database. My manager has stressed that once a month's figures have been reported, they must not change (even if the underlying data does, eg a record from February is deleted and the figures drop by 1, when it comes to March I must still report for February the figure that was reported originally, even though it is now technically incorrect). I understand his reasoning for wanting this, but as a data guy I find this very frustrating.

It is a complex report based on lots of pivot tables, and I have created a lot of manual VBA macros that refresh each pivot table, and copy and paste value the figures below it, which is what feeds the charts. As I'm the only person that can use VBA, this makes it impossible for anyone else to add any extra charts, as it has become so complex. So currently, I've set up an automated report, but it is a total nightmare adding / amending new charts.

I was wondering if anyone could think of any simpler alternative methods I could incorporate? I would much rather just have the report feed off of live pivot tables, but I just connot think of a way that I could use pivot tables and effectivly "freeze" old, already reported data.

Any suggestions?

 

[CSyx]

phil152003,

You are far more advanced than I am in Excel...but I have used a circular formula as a time stamp before. It pulls data based on a cell value and then stops. Hopefully it will give you some ideas...

<code style="margin: 0px; padding: 0px; border: none; font-size: 12px; color: rgb(51, 51, 51); line-height: 22px;">=IF(C3<>"",IF(B3="",NOW(),B3),"")</code> in cell B3 and iterations need to be enabled.

Good luck!

 

[phil152003]

phil152003,

You are far more advanced than I am in Excel...but I have used a circular formula as a time stamp before. It pulls data based on a cell value and then stops. Hopefully it will give you some ideas...

<CODE style="BORDER-BOTTOM: medium none; BORDER-LEFT: medium none; PADDING-BOTTOM: 0px; LINE-HEIGHT: 22px; MARGIN: 0px; PADDING-LEFT: 0px; PADDING-RIGHT: 0px; COLOR: rgb(51,51,51); FONT-SIZE: 12px; BORDER-TOP: medium none; BORDER-RIGHT: medium none; PADDING-TOP: 0px">=IF(C3<>"",IF(B3="",NOW(),B3),"")</CODE> in cell B3 and iterations need to be enabled.

Good luck!

Interesting idea... although I tried replicated your formula and I couldn't get it to work. What's the maximum iternations? I tried on the default 100, and set to 1, but the output of the cell was always 0, rather than the current date. Any suggestions?

 

[phil152003]

Essentially the problem I have at the minute is that if I put in any cell (e.g E14):

=E14=""

It always returns false, so in terms of the formula you posted, it would never get to the "NOW()" section of the formula.

 

[CSyx]

phil152003,

The formula should check cell C3. If it is blank then B3 (where the formula is entered) will remain blank. In the event C3 is not blank it should run the if formula and place the time stamp in B3. I have iterations set at 100...but I honestly don't know how much of an impact that will make.

 

[jigglypuff]

http://www.mrexcel.com/forum/excel-questions/396311-date-stamp-when-cell-changed.html

maybe this thread can help you! i think i am having the same problem in different ways, but i am far less advanced than you are though =(