Formula?

Todd_M · Apr 25th, 2002, 07:51 PM

If I have twelve sheets named Jan,Feb,Mar.....Dec, all in order from Jan-Dec. A formula in cell J2 that adds cell I2 and the last sheet's cell I2 plus as many more sheets cells I2 that there is untill Jan came up. So if sheet("July") was showing, then cell J2 on the July sheet would read all sheets from June to Januraury, cell"s I2 to get a "Year to date Total" in cell of July's cell J2. Can anyone help me w/ this formula going in to a formula bar on the spreadsheet?

Brian from Maui · Apr 25th, 2002, 08:01 PM

Todd,

Just add from DEC backwards. That is, in your DEC worksheet in J2:

=sum(nov!J2+dec!I2)

In you NOV worksheet in J2
=sum(oct!J2+nov!I2) etc....

Actually it doesn't matter forward or backwards

[ This Message was edited by: Brian from Maui on 2002-04-25 19:04 ]

Todd_M · Apr 26th, 2002, 08:34 AM

Brian, thanks for your help-
The formula:

=sum(nov!J2+dec!I2)

does part of what Im trying to accomplish except is there any way of not having to list the actuall name of the sheets. In other words if I was to put that formula in the sheet DEC , cell J2, how could you write it like this:

=sum(previose sheet!J2+this sheet!I2)

This would save me much time considering theres 25 years (1 sheet for each month = 300 sheets) worth of sheets that can just copy and paste this formula with out me having to change the sheet name each time. I assuming I would have to change just Jan sheet formula, which is fine.

Brian from Maui · Apr 26th, 2002, 09:23 AM

Todd,

The DEC sheet would have the YTD totals. You can link the DEC sheet with another sheet with all names on that sheet. If I'm correct, you can't do 3-D referencing with worksheets. You could either copy and paste the formula or copy the whole sheet for the 12 months.

Chris Davison · Apr 26th, 2002, 12:25 PM

Mark O'Brien rang me this morning pleading with me to solve this via the =INDIRECT function

(I'll admit, I couldn't quite figure out how to convert a textual representation of a month's name into something useable, so anyone who can slim this crap formula down a bit is very welcomed)

in your February sheet, in cell J2, try this :

=INDIRECT(VLOOKUP(RIGHT(CELL("filename",$J$2),3),{"Feb","Jan";"Mar","Feb";"Apr","Mar";"May","Apr";"Jun","May";"Jul","Jun";"Aug","Jul";"Sep","Aug";"Oct","Sep";"Nov","Oct";"Dec","Nov"},2,0)&"!J2")+I2

if it looks good, simply copy to cells J2 in your other sheets

I assumed you had all your sheet tabs abbreviated to 3 letters, per the Excel standard abbreviation - if you haven't, just amend the VLOOKUP array accordingly

(this assumes cells i2 are some sort of monthly SUM)

Chris Davison · Apr 26th, 2002, 12:36 PM

Toad,

your first post says 12 sheets

your next post says 300 sheets

*Please* tell me you have lots of workbooks, each with 12 sheets in them... hence your 25 years

don't even *think* of informing us that your sheets names are all in one workbook and are something like Nov 85, Dec 85, Jan 86, Feb 86 etc etc etc

croweld89 · Apr 26th, 2002, 12:59 PM

A solution that I use is a 12 month embeded IF Statement. It's pretty hairy, but, yes it is possible

Put the current month number in a hidden cell on the current sheet and place the following formula in the necessary cells (J2)

Say for June we hide 6 in cell m2

=if(or($m$2=1,$m$2=2,$m$2=3,$m$2=4,$m$2=5,$m$2=6),if($m$2=1,sum('Jan'!:'Jan'!j2,if($m$2=2,sum('Jan'!:'Feb'!j2,if($m$2=3, sum('Jan'!:"Mar'!j2,if($m$2=4,sum('Jan'!:'Apr'!j2, if($m$2=5,sum('Jan'!:'May'!j2,sum('jan'!:'May'!j2)))))), if(or($m$2=7,$m$2=8,$m$2=9,$m$2=10,$m$2=11,$m$2=12),if($m$2=7,'Jan'!:'Jul'!j2,if($m$2=8,'Jan'!:'Aug'!j2,if($m$2=9,'Jan'! :'Sep'!j2,if($m$2=10,'Jan'!:'Oct'!j2,if($m$2=11,'Jan'!:'Nov'!j2,if($m$2=12,'Jan'!:'Dec'!j2))))))))

I think you get the jest of the formula, I think the formula can hold up to 26ish embeded if statements

Todd_M · Apr 26th, 2002, 04:05 PM

Thankyou for your help Chris and croweld89. I tried croweld89 formula and I keep getting errors, plus I realy didnt want to go into each sheet and assign a number in cell M2.

Chris Im relativly new to excel and according to what I have done in the past and what I read it shoudnt be a problem to have 300 sheets in one workbook(But *please* tell me if im wrong, becouse then I need to redo this another way). I put your code into the Feb sheet and it read the total of I2. But it did not read the cell I2 in sheet Jan. I put the formula in the Jan and Mar sheet and it reads "#value". Cell I2 on each sheet gives me a months total from amounts list in column E. What would be a code in cell J2 that would read I2 in this sheet and cell J2 in the last sheet. This would give me a "year to date total" . But is It possible not to name the last sheet in the formula, instead like in code x=lastsheet so cell j2 would read:

=$I$2 + x???
All my sheets are in order, so this would work. Thanks again- Todd

Aladin Akyurek · Apr 26th, 2002, 04:37 PM

Quote:

On 2002-04-26 15:05, Todd_M wrote:
Thankyou for your help Chris and croweld89. I tried croweld89 formula and I keep getting errors, plus I realy didnt want to go into each sheet and assign a number in cell M2.

Chris Im relativly new to excel and according to what I have done in the past and what I read it shoudnt be a problem to have 300 sheets in one workbook(But *please* tell me if im wrong, becouse then I need to redo this another way). I put your code into the Feb sheet and it read the total of I2. But it did not read the cell I2 in sheet Jan. I put the formula in the Jan and Mar sheet and it reads "#value". Cell I2 on each sheet gives me a months total from amounts list in column E. What would be a code in cell J2 that would read I2 in this sheet and cell J2 in the last sheet. This would give me a "year to date total" . But is It possible not to name the last sheet in the formula, instead like in code x=lastsheet so cell j2 would read:

=$I$2 + x???
All my sheets are in order, so this would work. Thanks again- Todd

Chris's formula would require that you treat Jan sheet in a special way:

In J2 of the Jan sheet enter:

=I2

Each of the Feb, Mar, ... sheets just enter the formula he provided in J2.

Chris Davison · Apr 26th, 2002, 08:27 PM

Todd,

I'm a bit nervous about your 300 sheets....

not cos there's so many, but because you can only have 12 named Jan through to Dec, then the sheet names will have some other name structure (ie years or [2], [3]) appended to them (excel only allows unique sheet names, no repetiotions), at which point my formula fails as it's looking at the last 3 letters of the sheetname

after your 12th sheet named "Dec" what's the next sheetname ?

_________________
Hope this helps,
Chris

edit - also, don't go redesigning just cos a formula doesn't work ! that's the tail wagging the dog

as long as the sheet names follow some sort of recogniseable logic, we can easily come up with something suitable, even if it's 999 sheets

[ This Message was edited by: Chris Davison on 2002-04-26 19:30 ]

Apr 26th, 2002, 12:25 PM	#5
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Mark O'Brien rang me this morning pleading with me to solve this via the =INDIRECT function (I'll admit, I couldn't quite figure out how to convert a textual representation of a month's name into something useable, so anyone who can slim this crap formula down a bit is very welcomed) in your February sheet, in cell J2, try this : =INDIRECT(VLOOKUP(RIGHT(CELL("filename",$J$2),3),{"Feb","Jan";"Mar","Feb";"Apr","Mar";"May","Apr";"Jun","May";"Jul","Jun";"Aug","Jul";"Sep","Aug";"Oct","Sep";"Nov","Oct";"Dec","Nov"},2,0)&"!J2")+I2 if it looks good, simply copy to cells J2 in your other sheets I assumed you had all your sheet tabs abbreviated to 3 letters, per the Excel standard abbreviation - if you haven't, just amend the VLOOKUP array accordingly (this assumes cells i2 are some sort of monthly SUM) __________________ :: Pharma Z - Family drugstore ::

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 25th, 2002, 07:51 PM	#1
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	If I have twelve sheets named Jan,Feb,Mar.....Dec, all in order from Jan-Dec. A formula in cell J2 that adds cell I2 and the last sheet's cell I2 plus as many more sheets cells I2 that there is untill Jan came up. So if sheet("July") was showing, then cell J2 on the July sheet would read all sheets from June to Januraury, cell"s I2 to get a "Year to date Total" in cell of July's cell J2. Can anyone help me w/ this formula going in to a formula bar on the spreadsheet?

Apr 25th, 2002, 08:01 PM	#2
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Todd, Just add from DEC backwards. That is, in your DEC worksheet in J2: =sum(nov!J2+dec!I2) In you NOV worksheet in J2 =sum(oct!J2+nov!I2) etc.... Actually it doesn't matter forward or backwards [ This Message was edited by: Brian from Maui on 2002-04-25 19:04 ]

Apr 26th, 2002, 08:34 AM	#3
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Brian, thanks for your help- The formula: =sum(nov!J2+dec!I2) does part of what Im trying to accomplish except is there any way of not having to list the actuall name of the sheets. In other words if I was to put that formula in the sheet DEC , cell J2, how could you write it like this: =sum(previose sheet!J2+this sheet!I2) This would save me much time considering theres 25 years (1 sheet for each month = 300 sheets) worth of sheets that can just copy and paste this formula with out me having to change the sheet name each time. I assuming I would have to change just Jan sheet formula, which is fine.

Apr 26th, 2002, 09:23 AM	#4
Brian from Maui Board Regular Join Date: Feb 2002 Posts: 7,743	Todd, The DEC sheet would have the YTD totals. You can link the DEC sheet with another sheet with all names on that sheet. If I'm correct, you can't do 3-D referencing with worksheets. You could either copy and paste the formula or copy the whole sheet for the 12 months.

Apr 26th, 2002, 12:36 PM	#6
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Toad, your first post says 12 sheets your next post says 300 sheets Please tell me you have lots of workbooks, each with 12 sheets in them... hence your 25 years don't even think of informing us that your sheets names are all in one workbook and are something like Nov 85, Dec 85, Jan 86, Feb 86 etc etc etc

Apr 26th, 2002, 12:59 PM	#7
croweld89 New Member Join Date: Mar 2002 Posts: 34	A solution that I use is a 12 month embeded IF Statement. It's pretty hairy, but, yes it is possible Put the current month number in a hidden cell on the current sheet and place the following formula in the necessary cells (J2) Say for June we hide 6 in cell m2 =if(or($m$2=1,$m$2=2,$m$2=3,$m$2=4,$m$2=5,$m$2=6),if($m$2=1,sum('Jan'!:'Jan'!j2,if($m$2=2,sum('Jan'!:'Feb'!j2,if($m$2=3, sum('Jan'!:"Mar'!j2,if($m$2=4,sum('Jan'!:'Apr'!j2, if($m$2=5,sum('Jan'!:'May'!j2,sum('jan'!:'May'!j2)))))), if(or($m$2=7,$m$2=8,$m$2=9,$m$2=10,$m$2=11,$m$2=12),if($m$2=7,'Jan'!:'Jul'!j2,if($m$2=8,'Jan'!:'Aug'!j2,if($m$2=9,'Jan'! :'Sep'!j2,if($m$2=10,'Jan'!:'Oct'!j2,if($m$2=11,'Jan'!:'Nov'!j2,if($m$2=12,'Jan'!:'Dec'!j2)))))))) I think you get the jest of the formula, I think the formula can hold up to 26ish embeded if statements

Apr 26th, 2002, 04:05 PM	#8
Todd_M Board Regular Join Date: Feb 2002 Posts: 117	Thankyou for your help Chris and croweld89. I tried croweld89 formula and I keep getting errors, plus I realy didnt want to go into each sheet and assign a number in cell M2. Chris Im relativly new to excel and according to what I have done in the past and what I read it shoudnt be a problem to have 300 sheets in one workbook(But please tell me if im wrong, becouse then I need to redo this another way). I put your code into the Feb sheet and it read the total of I2. But it did not read the cell I2 in sheet Jan. I put the formula in the Jan and Mar sheet and it reads "#value". Cell I2 on each sheet gives me a months total from amounts list in column E. What would be a code in cell J2 that would read I2 in this sheet and cell J2 in the last sheet. This would give me a "year to date total" . But is It possible not to name the last sheet in the formula, instead like in code x=lastsheet so cell j2 would read: =$I$2 + x??? All my sheets are in order, so this would work. Thanks again- Todd

Apr 26th, 2002, 08:27 PM	#10
Chris Davison MrExcel MVP Join Date: Feb 2002 Location: Millbank, London, UK Posts: 1,790	Todd, I'm a bit nervous about your 300 sheets.... not cos there's so many, but because you can only have 12 named Jan through to Dec, then the sheet names will have some other name structure (ie years or [2], [3]) appended to them (excel only allows unique sheet names, no repetiotions), at which point my formula fails as it's looking at the last 3 letters of the sheetname after your 12th sheet named "Dec" what's the next sheetname ? _________________ Hope this helps, Chris edit - also, don't go redesigning just cos a formula doesn't work ! that's the tail wagging the dog as long as the sheet names follow some sort of recogniseable logic, we can easily come up with something suitable, even if it's 999 sheets [ This Message was edited by: Chris Davison on 2002-04-26 19:30 ]