year to date chart

Microsoft Excel 2010
June 18, 2010 at 21:31:44
Specs: Windows 7
im making a chart that shows running totals for the week the month year etc. every week i reset the stats for the week and when i do it subtracts whatever the weekly number was from the month year etc what is the formula to tell it not to subtract but to stay at highest number when i reset the week stats and continue counting

See More: year to date chart

Report •

June 19, 2010 at 07:12:03

I suggest you read your own post from the point of view of someone who has never seen your worksheets, or your charts and has no idea what formulas have been used, and has no idea about what data is being used, or where it is stored.

From that point of view it should be clear that your question is pretty much unanswerable!

I suggest that you identify where your problem is and provide clear information on what is not working, including the relevant formula(s), the nature and location of data etc. etc.

Then read your question and consider if there is enough information in the question for someone not sitting in front of your PC to potentially come up with a solution.


Report •

September 22, 2010 at 19:20:03
I believe what the original post was trying to say , is also the exact problem I am having. He is trying to create a spreadsheet to compile YTD "Year to date" MTD "Month to date" and WTD "Week To Date" for financial information.

I apologize for the "explanations of abbreviations" just trying to be perfectly clear :)

I am running into the same problem, at work I had Excel thrust upon me and was told I had to learn it and convert all of my previously hand written reports onto a automated report that can be filled out by anyone.

The problem is a lack of knowledge on my part:
I have created a work book that has 3 sheets titled WTD, MTD, YTD

WTD: has basic =SUM() functions that add up and calculate all revenue's for the operations and register totals for my work.

MTD: is using functions to take the week totals from my WTD Sheet using the =SUM (WTD!WTDTOTAL) function and is supposed to place them in the Week1 through Week4 Columns, which are then subjected to formulas to compare them to previous year totals for that month.

YTD: is to take the totals from the sheet MTD using the =SUM(MTD!MTDTOTALS) function and place them in YTDTOTAL

The problem I am having as was the previous poster, is at the end of each week all of the cells used to calculate all of the WTD totals on the first sheet has to be reset (deleted) for the new week numbers to be input, and the functions I am using do not save the previous information into a separate cell that shows a running total, instead all the calculations are lost.

Each sheet works great individually, if I had supervisors that were happy with someone just manually inputting the information each week/month onto the next sheet.... but they don't want that. Is there a template or a tutorial that I can be directed too, or is there a simple function example I can use to get the running totals in a final cell that doesn't disappear when the cells used to get the total are emptied?

Report •

September 22, 2010 at 20:45:25
re: is there a simple function example I can use to get the running totals in a final cell that doesn't disappear when the cells used to get the total are emptied?

No cell that contains a formula that is dependent on data from other cells can retain that dependent value - and the formula - when the data is deleted.

Will your roof stay up if you delete the walls?

There are 2, OK 2.5, ways to accomplish your goal:

1 - Select the cell with the final value and do a Copy...Edit...PasteSpecial...Values. Of course, you will lose the formula.

2 - Use VBA to replace the formula with the value:

Sub StaticValue()
  Range("A1") = Range("A1").Value
End Sub

Once again, you will lose the formula.

2.5 - Use VBA to copy the value to another cell.

Sub CopyStaticValue()
  Range("B1") = Range("A1").Value
End Sub

Your original cell will still contain the formula but you will have a copy of the last value it returned in another cell.

Report •

Related Solutions

Ask Question