trying to return a year to date amount

**bumpty** · 03-04-2011, 10:53 AM

Can anyone assist me with this

I have a spreadsheet that shows a budget by month and I want to create a rule that when I change the month date that the YTD amount automatically changes

ie in March - I put MAR in the month colum and then on the YTD colum it automatically adds up the Jan - Marc budgets to give me the total

is there a way of doing this rather than having to add each line manually

**brokenbiscuits** · 03-04-2011, 10:57 AM

Hi Bumpty

Could you post a sample of your data? It'll be much easier to give a concise answer as there are probably a few ways to do this depending on how your data is organised.

**bumpty** · 03-04-2011, 11:18 AM

yes sorry no problem
please see attached

I basically have many tabs and want to put in JAN and the month automatically picks up that month budget and the same for the YTD
that way I can avoid having to manually update so many tabs individually just by altering that month somewhere it automaically updates all my Month and YTD tabs

**Worldtraveller** · 03-04-2011, 12:17 PM

You can do it using OFFSET.

Check out the OFFSET tutorial (I did a search on the site and found this:

Mind you, I didn't create it, and I'm still trying to wrap my head around applying offset to my application, but this example does exactly what you want, I think.

**brokenbiscuits** · 03-04-2011, 12:20 PM

See attached.

The formulae all link into cell B12, but you could easily change this to a cell on a cover sheet to which you can link all tabs.

You could take the automation one step further by changing the values in B2:M2 to 01-Jan, 01-Feb, etc, and change the format to MMM, then replacing the $B$12 in the formulae with MONTH(today()), which would automatically update values based on today's date.