Changing a cell reference

[account2009]

Hi

Please can you have a look at my sample file
I have three sheets - Actual( which has actual figures from Jan - Dec), Budget ( figures from Jan - Dec) and a Summary sheet ( which is just the summary for the current month, say July and compares the budget VS Actual)

Every month i need to change the summary sheet to the next months set of figures, say Augusts etc etc.

Currently i've been using find and replace, and changed the formula to the correct months column - but its a rather large spreadsheet( its a management accounts pack and has many many more sheets that link up - like lasts years comparison etc etc)

Is there any easy way of linking ( without VBA) or anything complex, so that each month i can change my data easily?

Thanks a mill

[DonkeyOte]

Welcome to the Board.

IMO the sample file leaves a few key questions unanswered given it's set to use Jan data only and thus does not allow us to determine if the figures being used and retrieved are periodic or YTD ? Presumably they are Periodic, in which case the next question becomes: are you looking to return the cumulative total (ie Jan:Jul) or merly the periodic total (Jul only) to your Summary sheet ?

Moving on to the Summary sheet detail itself - are you looking to group multiple line items from Actual/Budget on your Summary sheet as implied with the Sales 1+2 line ... if so the next question is: are the layouts of each sheet fixed over time or do you insert new sales lines etc as and when the need arises onto Actual/Budget and/or alter the summary groupings ? Assuming you wish to amalgamate IMO you should look to revise Actual/Budget sheets to incorporate a further column adjacent to the detail line which identifies the Summary bucket into which the values are to be assigned.

In essence what you want to do is relatively straightforward, however, before providing an example it would be a good idea to flesh out the requirements.

[martindwilson]

see attached just change month in summary b2

[account2009]

Thanks Martindwilson! Its works. Thank you for your help!!!

Thanks DonkeyOte for looking into it, Much appreciated. Yes the figures being used are periodic, and i'm looking to return the periodic total ( and NOT cumulative) to my summary sheet. Yes i am looking at grouping multiple lines - but No, i wouldnt want to insert new lines - so the sheets would remain pretty much static.