Software

Ask something
Discussion Locked

microsoft excel formula help

0 helpful
4 replies

by hotrodgers

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

i have excel 2000...how can you take a summed number in a pivot tables output and store that number in another location to retain and add numbers to it once a day without losing the original amount? everytime i change the numbers everyday, and i want to keep a running total on how many i have used to date. the pivot table shows me the amount on the days amount but changes the next day when i wipe out the old number and put the new number in. i want to be able to keep each days numbers in history and continually add to them by the amount on whatever part # has been done that day. there are over 500 part #'s and we may only do 5 different part numbers a day, but i want to look at this 6 months from now and see how many of a particular part # i have done to that date...any suggestions?
thanks

4 replies

by dklippert

Define three named ranges. "Total" is the dailytotal. "OldTotal" is the to-date grand total. "NewTotal" is used to pull in the daily numbers.

For the ?Total? named range, use a formula like:
=GETPIVOTDATA(ATable,?Part Numbers)?

Try a macro along these lines.

Sub Accumulator()
Range("NewTotal").Select
ActiveCell.FormulaR1C1 = "=OldTotal+Total"
Selection.Copy
Selection.PasteSpecial Paste:=xlValues
Range("OldTotal").Select
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd
Application.CutCopyMode = False
End Sub

This discussion is closed to new replies.