subtotaling hours
Posted by norman jones on December 02, 2001 8:36 AM
my head is going to burst, all i want to do is add hours, A1 to B1, use that as a subtotal, then C1 to be the overall total. example first entry:-
A1= 12:00,B1 wouldcalculate to 12:00, and C1 would also caculate to 12:00.
second entry:-
A1=10:00, B1 should calculate and read 22:00, C1 should also calculate and read 22:00. i keep getting the circular reference, it is doing my head in, someone help please, i'll buy you a beer!
ps i'm not very cleaver so make the answer simple please, or better yet email me an example in excel
Posted by Tom D. on December 02, 2001 8:52 PM
It seems like you are trying to add a number to a formula that contains a reference to a cell, the original contents of which you are erasing.
How about a simple macro?
Step 1 under "Tools", Macro, press "Record new macro"
Step 2 - in any cell, put in some gobbledygook; press enter, then press "end Macro" (if the end macro button is not available, go back to tools, macro, and find it there.
Step 3 - Hit Alt F8, select the macro, and press "Edit"
Step 4 - Erase everthing but the first and last lines of the macro, and copy the below to that area:
Range("B1") = Range("B1") + Range("A1")
Range("C1") = Range("B1")
Step 5 - run the macro everytime you change A1. You can do this in 3 different ways:
1) Selecting the macro each time;
2) Running the macro from a button on your tool bar; or
3) running the macro from a button on the spreadsheet.
For the first method, there is nothing to do. Whenever you want to run the macro, Select "Tools", Macro, select the macro from the list, and press run. (Shortcut to the list is Alt F8)
Numbers 2 & 3 are 1 button shortcuts to the above.
For number 2, press "View", "Toolbars", "Customize", "Commands", "Macro". Now drag the custom button to your tool bar. Close the "customize" window. Now press the custom button on your tool bar, and assign the macro from above. From then on, just hit the button to do the addition.
For number 3, Follow the steps at number 2, but select "Forms" instead of "Macros". Drag the "Button" icon to your tool bar, then close the customize box. Put a button on your spreadsheet by clicking on the button icon on the tool bar, then moving the cursor to where you want the button to be, hold down the left mouse button, and draw to the opposite corner of where you want the button to be. When you release the mouse, the box to assign a macro will pop up. Assign the macro and you are done.
The button on your spreadsheet can be renamed, and colors changed by puttin the mouse over it and pressing the right mouse button.
To remove the icons from your toolbar, press "View", "Toolbars", "Customize", then drag the icon(s) off the toolbar.
Does this qualify for the beer or is it too long?
Posted by norman jones on December 03, 2001 4:29 AM
sir you are a gentleman, works like dream, send me your address and i will try and get you some beer
