Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying & incrementing formulae across open worksheets
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying & incrementing formulae across open worksheets
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" wrote: 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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying & incrementing formulae across open worksheets
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" wrote: 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" wrote: 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. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying & incrementing formulae across open worksheets
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. "carrach" wrote: 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" wrote: 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" wrote: 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. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
copying & incrementing formulae across open worksheets
Try selecting all the sheets by clicking with shift i.e. Feb:Dec
Then enter in D51**: =D50+SUM(IF(1,'*'!D50)) Or use: =D50+SUM('*'!D50) then editreplace: ,*) 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. "carrach" wrote: 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" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an incrementing value in a cell point to different worksheets? | Excel Worksheet Functions | |||
copying formulae | Excel Discussion (Misc queries) | |||
Copying worksheets with formulae between workbooks | Excel Discussion (Misc queries) | |||
copying formulae | Excel Worksheet Functions | |||
How do I prevent a formula element from incrementing when copying | Excel Worksheet Functions |