Software

Ask something
Discussion Locked

excel formula help

0 helpful
5 replies

by hotrodgers

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

i have a file that has 2 worksheets in it. one is the data entry tab the other is the data base tab. the data entry tab has information entered in it throughout the day. i need to extract that information daily at 6pm to get the final entries andput it in the data base sheet in a new column to keep a running total of these numbers. ok...the data entry sheet is wiped out everymorning to start the new day. the data base field has more than 500 part numbers in it. i need the running totals to access the data entry sheet every afternoon and look up what part numbers are listed there then get the mold totals from a column in the data entry sheet that corresponds to that part number and put them in the data base totals column, and do thisdaily without the totals column losing the original number brought over each morning when they wipe out the data entry sheet. bottom line i need to keep a running total on mold totals by part number, and do this when the daily numbers are wiped outevery morning. if this is not confused everybody...i would appreciate any help...thanks
steve

5 replies

by afong

Let me see if I understand this. You can daily entries in Sheet1 and a running total in Sheet2. Each day, the total from Sheet1 is added to the total on Sheet2 to create the running total.

You can create a pivot table based on Sheet1, which canautomatically summarize your amounts by part numbers (see Help or Excel reference book on creating a pivot table). Also, when creating the pivot table, select all the columns used for your data entries (e.g., $A:$F) instead of a range (e.g., A1:F100). You can then refresh your pivot table based on new data each day regardless of how many rows the new data takes up. Then use the summarized totals from the pivot table to update Sheet2.

For Excel tips, try www.j-walk.com/ss/. John Walkenback also writes an excellent book called Excel 2000 Formulas filled with niffy tips, like creating charts that can automatically include new data.

by dklippert

Try a macro along these lines.
There are three named ranges. "Total" is the grand total on the Daily Entry tab. "OldTotal" is the to-date grand total. "NewTotal" is used to pull in the daily numbers.

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.