copying & incrementing formulae across open worksheets

[carrach]

Hi,
I have a workbook containing several worksheets i.e one worksheet for each
month. I want to use a year to date colum within each worksheet which means
using a formulae that increments across each worksheet.(i.e. in 'feb' sheet
add 'jan year to date' & 'feb actual' figures together and then in March
sheet add feb 'year to date' to March actual figures etc etc) Is this
possible? I have been unable to find any help in any books as most options
seem to be about copying one item to many worksheets which is not the same.

 

[Ron@Buy]

carrach, if I understand you correctly you want to bring forward Jan total
and add it to Feb total and so on?
I assume your Janauary worksheet tab is 'Jan' and February is 'Feb'
So lets assume your Months' total appears in the same cell D50 on each
worksheet
Then on Feb worksheet, in cell D51 enter formula
=Jan!D50+D50
In Mar worksheet cell D51 enter formula
=Feb!D51+D50 and so on, for each month's worksheet
Change cell reference to suit your senario
Hope this helps

 

[carrach]

Thank you Ron,
I had already done the formulae as you stated, what I was hoping to find was
an easier way of copying the formulae across the worksheets, and having the
worksheet address within the formulae incremented with each step. Maybe it
is not possible and I will have to update the formulae on each sheet
individually and then copy it down the column.
Thank you anyway.

 

[Ron@Buy]

Carrach, there is'nt a simple way to do what you want. A possible part way
solution would be to set up the 'feb' worksheet as you want it, (plus making
allowance for 31 days) then copy it to each of the remaining month worksheets
then on each worksheet do a global find and replace ( i.e. replace 'feb' with
'mar' and so on). For future use you could record a macro which will the do
your find & replace for you.
Of course you write your own VBA code.

 

[Lori Miller]

Try selecting all the sheets by clicking with shift i.e. Febec
Then enter in D51**: =D50+SUM(IF(1,'*'!D50))
Or use: =D50+SUM('*'!D50) then edit>replace: ,*) with nothing.

** The '*' automatically converts to all sheets other than the
one in which the formula is entered. This shortcut applies to all
versions except xl2002 where it can be unstable.