Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Cumulative Summing
I am successfully doing cumulative summing for columns Jan-05 thru Dec-05
based on cell I1 which puts today's date into text by =TEXT(TODAY(),"mmm-yy"). I then use the formula =SUM($O3:INDEX($O3:$Z3,MATCH($I$1,$O$2:$Z$2,0))) which compares today's date in text vs what I have manually inputted for month headings in text. Everything works fine as long as everyone uses an English copy of Excel. This is not the case in our France location however so when they open up the spreadsheet, today's date in text is showing as Févr-05 which of course does not match the English headings of Feb-05. I could of course input the month headings in text in French so it would match, but then it would not work when someone with an English copy opens up the spreadsheet. So I either need a solution to this translation issue, or a new way to do cumulative summing where I don't need to change dates into text. Any help would be immensely appreciated. |
#2
|
|||
|
|||
Hi,
How about adjusting the column headings to be, for instance for Jan 04 =TEXT(DATE(2004,01,01),"mmm-yy")? Cheers, Jon "Carpie" wrote: I am successfully doing cumulative summing for columns Jan-05 thru Dec-05 based on cell I1 which puts today's date into text by =TEXT(TODAY(),"mmm-yy"). I then use the formula =SUM($O3:INDEX($O3:$Z3,MATCH($I$1,$O$2:$Z$2,0))) which compares today's date in text vs what I have manually inputted for month headings in text. Everything works fine as long as everyone uses an English copy of Excel. This is not the case in our France location however so when they open up the spreadsheet, today's date in text is showing as Févr-05 which of course does not match the English headings of Feb-05. I could of course input the month headings in text in French so it would match, but then it would not work when someone with an English copy opens up the spreadsheet. So I either need a solution to this translation issue, or a new way to do cumulative summing where I don't need to change dates into text. Any help would be immensely appreciated. |
#3
|
|||
|
|||
Use formulas for your headers as well
=TEXT(DATE(2005,2,1),"mmm-yy") (for February as an example) then your headers will be translated as well -- Regards, Peo Sjoblom "Carpie" wrote in message ... I am successfully doing cumulative summing for columns Jan-05 thru Dec-05 based on cell I1 which puts today's date into text by =TEXT(TODAY(),"mmm-yy"). I then use the formula =SUM($O3:INDEX($O3:$Z3,MATCH($I$1,$O$2:$Z$2,0))) which compares today's date in text vs what I have manually inputted for month headings in text. Everything works fine as long as everyone uses an English copy of Excel. This is not the case in our France location however so when they open up the spreadsheet, today's date in text is showing as Févr-05 which of course does not match the English headings of Feb-05. I could of course input the month headings in text in French so it would match, but then it would not work when someone with an English copy opens up the spreadsheet. So I either need a solution to this translation issue, or a new way to do cumulative summing where I don't need to change dates into text. Any help would be immensely appreciated. |
#4
|
|||
|
|||
Eureka! You two are the best. Sometimes the simple answer is always the one overlooked. "Peo Sjoblom" wrote: Use formulas for your headers as well =TEXT(DATE(2005,2,1),"mmm-yy") (for February as an example) then your headers will be translated as well -- Regards, Peo Sjoblom "Carpie" wrote in message ... I am successfully doing cumulative summing for columns Jan-05 thru Dec-05 based on cell I1 which puts today's date into text by =TEXT(TODAY(),"mmm-yy"). I then use the formula =SUM($O3:INDEX($O3:$Z3,MATCH($I$1,$O$2:$Z$2,0))) which compares today's date in text vs what I have manually inputted for month headings in text. Everything works fine as long as everyone uses an English copy of Excel. This is not the case in our France location however so when they open up the spreadsheet, today's date in text is showing as Févr-05 which of course does not match the English headings of Feb-05. I could of course input the month headings in text in French so it would match, but then it would not work when someone with an English copy opens up the spreadsheet. So I either need a solution to this translation issue, or a new way to do cumulative summing where I don't need to change dates into text. Any help would be immensely appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Summing Time | Excel Discussion (Misc queries) | |||
Cumulative Frequency | Charts and Charting in Excel | |||
How to make cumulative totals appear on a graph | Excel Discussion (Misc queries) | |||
Cumulative Total | Excel Discussion (Misc queries) | |||
Summing Sheets - SUMIF ? | Excel Worksheet Functions |