I have two spreadsheets within the same workbook. The first spreadsheet (HOURS) allows data entry across multiple days/weeks across multiple work tasks. The second spreadsheet (TOTAL HOURS) is intended to keep a running total of the data entered across the multiple work tasks whenever data is entered in the HOURS spreadsheet. Does anyone know how I can automatically update the total in TOTAL HOURS on an ongoing basis using base EXCEL commands? I can easily copy from the appropriate cell(s) in HOURS to the appropriate cell(s) in TOTAL HOURS; however when the cell(s) in HOURS change so do the cell(s) in TOTAL HOURS. Basically, I'm looking for a work in progress (WIP) cell that will maintain the total from the previous data entry in HOURS and allow me to add new hours as they are entered.
This is an example of the basic 'problem' in Excel for many - there are no formulas that you can use that won't update except if the formula also contains some kind of decision maker: usually the IF() function. A cell can either hold a formula that will (normally) update with any change to cells it references, or it can hold a value (something you type in) which is permanent.
Problem for that in cases like this there is no equivalent of a statement like:
=IF(X1="Don't Update",Keep Previous Results,Calculate New Results)
You have a couple of options:
#1 copy the total hours when you want to preserve them and perhaps copy them to the column right next to the column with the formulas in it. BUT instead of just copy and paste, use Copy then Edit --> Paste Special and choose the "Values" option. The value (results of the formulas) will be placed in the new cells and won't be updated with changes to the HOUR entries.
#2 ask for, or even record, a macro that will do the #1 actions for you when you need to create a record of the previous entry/entries. For us to provide one, we'd need to know the worksheet names involved and the cell addresses involved on both sheets. A sample workbook (with personal or confidential info removed) and posted to one of these free file hosting sites and made 'public' for us to look at would be marvelous. All you have to do then is post the link to the file that the site provides to you.
Windows OneDrive: http://onedrive.com
Or maybe I'm totally misunderstanding you. Does this describe what you are looking for?
Let's say at the start of some time period that both the plain HOURS and the Total Hours sheets look like this:
|The TOTAL HOURS sheet (and initially also the 'just' hours sheet)|
Then when the Hours sheet is used for the next period and looks like this:
|The 'just' HOURS Sheet|
You want the Total Hours sheet to look like this?
|The TOTAL HOURS sheet after new entries on the 'just' hours sheet.|
If that's what you're looking for, almost certainly you're going to need a macro solution.