Automatically change the location of a cell by month...

[MichaelCAT74]

I wonder if someone could help - I think it may be a VB macro and not a formula solution....

Currently I have 1 workbook with 2 worksheets (tabs). 1 is a top line summary report (tab called 'graphs') with the other a more detailed indepth spreadsheet (called 'data').

'Graphs' has a cell which references a monthly figure achieved from the 'data' detailed tab and is populated in 'graphs' as cell A10.

Currently, this formula/reference has to manually be changed each month to reflect the new month total. I.e. May is B6, June is B7 right through to April 12 being cell B17 on the 'data' worksheet.

Is there a formula or macro that I could use to automate this process so that it just changes or a field I could add where if I add July all the cells that require this type of change will just automatically reference to the right cells?

Best wishes

Michael

 

[villy]

If you could site a sample sheet it will be more clearer.Thanks

 

[MichaelCAT74]

I am having difficulty copying it over...

Basically the 'graph' sheet comprises of amongst other summary stats:

Month actual (cell A9)
(cell A10) = actual figure for the month as below.... (currently =Data!B8)
The 'data' sheet comprises of:

Month actual:

May (cell A6) 42 (cell B6)
June (cell A7) 50 (cell B7)
July (cell A8) 81 (cell B8)
etc