'Storing' and summing data before it is overwritten.

[gwoolley0302]

Hello there,

(Example file attached)

I have a set of data, whereby it records how much a list of companies have repaid (across several transactions each) (see cells A4:D14). The excel file is updated approx every 3 months to update how much each company has repaid - however this is done by overwriting the cells in column C - and sometimes the column D is completed to confirm when the cell was last updated.

What I'm needing to do - I would like track the 'repayment position' on a quarterly basis (rather than just having the latest scenario). So my thinking was to create a table like in cells G4:O10 - which records the quarterly position for each company, when it is available. This would ideally also aggregate amounts by summing the quarterly amounts for each company (e.g. by a sumifs formula).

Are there any ways for the Excel to 'capture' repayments numbers within a certain time period (e.g. within 30 days of a 'data check date') - e.g. automatically copy and paste them into a quarter by quarter table, before the data gets overwritten?

I would want to copy the formula into future quarters but leave as blank until the relevant time.

Is this possible (without VBA)?

(In the file I've added a second and third source table (A17:C27 and A30:C40) just to exemplify how the data looked like 3 months ago and how it might look in 3 months. However, to be clear, there is actually only one source table, that gets updated every 3 months. I'm attempting to extract data every quarter before the update (and overwrite) occurs.)

(I'm unable to download the Excel add-in to upload file sorry. So only a picture attached.)

Thanks, Greg

 

[gwoolley0302]

[Bump]

Just wondered if anyone had any ideas for this please? Along the lines of how I thought something might be possible? Or through a very different way?

Really appreciate anyone's help.