Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Update cells formula
Hello and thank you for at least attempting to answer my question.
I am a pilot, and I am attempting to create a running hours, dollars & cost per hour log in Excel for each aircraft I fly. My goal is to have a simple sheet wherein I enter the total of hours in one unprotected cell, the total dollars spent in the next unprotected cell; then press the €śUPDATE€ť button that has a macro assigned to it; and the sheet will add the today hours to the existing protected total hours cell, add the today dollars to the existing total dollars, and then clear the unprotected data-entry cells. I am not sure if this is just too simple, or just too hard; I am pretty good with Excel, but am stumped here. Thanks again for at least attempting! |
#3
|
|||
|
|||
Pilot
I personally would advise against the idea of "accumlator" cells. You have no "paper trail" to consult if an error in entry is made. You would be better off using a couple of helper columns to enter the data so's you can keep track of the entries. That said, here is a simple macro to do what you asked. Sub additup() With ActiveSheet ..Range("B1").Value = Range("B1").Value + Range("A1").Value ..Range("B2").Value = Range("B2").Value + Range("A2").Value ..Range("A1,A2").ClearContents End With End Sub Enter the values in A1 and A2 then hit the "Update" button to which you have assigned the code. To do this without a button, you can use use event code. See JE McGimpsey's site for this.... http://www.mcgimpsey.com/excel/accumulator.html Gord Dibben Excel MVP On Mon, 13 Jun 2005 08:31:03 -0700, "PilotNeedsHelp" wrote: Hello and thank you for at least attempting to answer my question. I am a pilot, and I am attempting to create a running hours, dollars & cost per hour log in Excel for each aircraft I fly. My goal is to have a simple sheet wherein I enter the total of hours in one unprotected cell, the total dollars spent in the next unprotected cell; then press the “UPDATE” button that has a macro assigned to it; and the sheet will add the today hours to the existing protected total hours cell, add the today dollars to the existing total dollars, and then clear the unprotected data-entry cells. I am not sure if this is just too simple, or just too hard; I am pretty good with Excel, but am stumped here. Thanks again for at least attempting! |
#4
|
|||
|
|||
Thanks, Don. This did work, and I thank you for your time and expertise!
"Don Guillett" wrote: how about this macro which could be a change_event macro in the sheet module to make it automatic when you enter something in range(e4) Sub newtotals() Range("f4") = Range("f4") + Range("e4") Range("e4").Clear End Sub -- Don Guillett SalesAid Software "PilotNeedsHelp" wrote in message ... Hello and thank you for at least attempting to answer my question. I am a pilot, and I am attempting to create a running hours, dollars & cost per hour log in Excel for each aircraft I fly. My goal is to have a simple sheet wherein I enter the total of hours in one unprotected cell, the total dollars spent in the next unprotected cell; then press the "UPDATE" button that has a macro assigned to it; and the sheet will add the today hours to the existing protected total hours cell, add the today dollars to the existing total dollars, and then clear the unprotected data-entry cells. I am not sure if this is just too simple, or just too hard; I am pretty good with Excel, but am stumped here. Thanks again for at least attempting! |
#5
|
|||
|
|||
Thanks, Gord. As I was 'testing' my sheet, I found the short-coming of no
original data to reference. Since I do keep a paper log, not a terrible issue on this on. BUT, I use spreadsheets for so much more, and have already used your recommended option on another spreadsheet. Thanks for your help! "Gord Dibben" wrote: Pilot I personally would advise against the idea of "accumlator" cells. You have no "paper trail" to consult if an error in entry is made. You would be better off using a couple of helper columns to enter the data so's you can keep track of the entries. That said, here is a simple macro to do what you asked. Sub additup() With ActiveSheet ..Range("B1").Value = Range("B1").Value + Range("A1").Value ..Range("B2").Value = Range("B2").Value + Range("A2").Value ..Range("A1,A2").ClearContents End With End Sub Enter the values in A1 and A2 then hit the "Update" button to which you have assigned the code. To do this without a button, you can use use event code. See JE McGimpsey's site for this.... http://www.mcgimpsey.com/excel/accumulator.html Gord Dibben Excel MVP On Mon, 13 Jun 2005 08:31:03 -0700, "PilotNeedsHelp" wrote: Hello and thank you for at least attempting to answer my question. I am a pilot, and I am attempting to create a running hours, dollars & cost per hour log in Excel for each aircraft I fly. My goal is to have a simple sheet wherein I enter the total of hours in one unprotected cell, the total dollars spent in the next unprotected cell; then press the €śUPDATE€ť button that has a macro assigned to it; and the sheet will add the today hours to the existing protected total hours cell, add the today dollars to the existing total dollars, and then clear the unprotected data-entry cells. I am not sure if this is just too simple, or just too hard; I am pretty good with Excel, but am stumped here. Thanks again for at least attempting! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
trying to create an (almost) circular formula between cells and data validated cells with lists | Excel Worksheet Functions | |||
Macro or Formula to remove Text from Cells | Excel Worksheet Functions | |||
pasting or moving formula cells without updating formulas | Excel Discussion (Misc queries) | |||
How To Use Cells Without Values in a Formula | Excel Worksheet Functions | |||
Excel - formula to calculate colored fill cells within a range wi. | Excel Worksheet Functions |