Dynamic Reporting in excel

Regular Visitor

I have some data in structure below:

  • Top row is the date in mmm-yy format (e.g. Apr-23)
  • 2nd row is an absolute sum of the 5 rows below it (e.g. SUM($B$3:$B$11))
  • Rows 3-11 have numbers entered

 

I then have a summary section which I want to show “start date”, “duration”, “end date” and “average in a year period (e.g. Apr23 – Mar24)”.  I want this summary to be dynamic, so that in the workings area mentioned above, if I were to move the data in rows 3-11 across by 2 columns (so from Apr-23 column to Jun-23 column) either by selecting it all and moving it or cut/paste, then the data in the summary will update to reflect this.

 

I’ve appeared to come close to success but then come unstuck when moving the “workings” data which has the wrong affects on the summary fields. What I’ve done so far is: -

 

For the start field in the summary I used =INDEX(!$B$1:$X$1,MATCH(TRUE,INDEX($B$2:$X$2>0,0),0)) – This works if I copy/paste the data in rows 3-11 into new columns and then delete the data from the previous columns

 

For the end field I attempted to do something similar finding the first blank cell in the range or cell that =0 in the range, but the problem is that if I do the above (copy and paste data and delete from previous columns) it see’s those now vacant cells as the first blank in range. So the range essentially needs to be starting from first cell in the range containing a value (start field above) and not the start of the complete range.

 

The duration field I envisaged just being the date in end field minus the date in the start field, or if it is easier to switch this round and get the start field, calculate the duration somehow (e.g. count cells with values in until first blank cell in range) and then add this to the start date to get the end date.

 

The average should be an absolute calculation calculating the average row 2 in a specified range (e.g. Apr-23 to Mar-24).

 

Any advice on how best to achieve this would be really appreciated!

0 Replies