Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
I've been working on a payroll workbook, and most everything is working the
way I want it to except the YTD totals. I want to update the YTD cell with the push of a button (or keystrokes) after the user finishes with the rest of the values. Help? |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Ted
Presumably you have a spreadsheet in which you update specific data and then get a YTD total at the end. I understand that you want to update the data first and then calculate the YTD total afterwards. The simplest way to do what you want is the following. In the Excel sheet select <Tools<Options and select the <Calculation tab. In the 'Calculation' part, set it to 'Manual' instead of 'Automatic'. This means that you spreadsheet will not update calculations until you ask it to. Once you have inputted your latest payroll data select <Tools<Options and go back into the <Calculations tab and press the <Calc Now (F9) button. This will now 'force' excel to update your YTD total. But, as you may have guessed, instead of going to <Tools<Options etc. you can simply press <F9 and this will force the update. I shows you the long way just for completeness. This is the easist way to what you are asking without using VBA etc. Please note that if you turn off automatic calculations then I would make sure your employees know so that there is no confusion over the numbers. Hope this helps... Regards Alex "Ted Dawson" wrote: I've been working on a payroll workbook, and most everything is working the way I want it to except the YTD totals. I want to update the YTD cell with the push of a button (or keystrokes) after the user finishes with the rest of the values. Help? |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Is that setting global, across all the sheets in the workbook, or can it be
set for each sheet? |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Ted
As far as I am aware, that is global i.e. applies to all sheets. Just tried it and it seems that way. There is another way around your problem but it involves VBA. Are you familiar with VBA? The solution to your problem would be quite simple and would be sheet specific. Is this of any interest? Alex "Ted Dawson" wrote: Is that setting global, across all the sheets in the workbook, or can it be set for each sheet? |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Yes, please elaborate. We're not scared of VBA.
"Alex" wrote in message ... Ted As far as I am aware, that is global i.e. applies to all sheets. Just tried it and it seems that way. There is another way around your problem but it involves VBA. Are you familiar with VBA? The solution to your problem would be quite simple and would be sheet specific. Is this of any interest? Alex "Ted Dawson" wrote: Is that setting global, across all the sheets in the workbook, or can it be set for each sheet? |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Ted
Ok. There are two parts to this. First is setting up the VBA code, the second part is getting it to work on your sheet. Part 1: With your Excel workbook open and the appropriate sheet, select ALT + F11. This will launch the Visual Basic Editor (VBE). In here now select <Insert<Module. This will insert a blank module into your VBE. Now in the blank module type the following (or cut and paste from here): Sub YTDcalc() Dim YTDtotal As Double YTDtotal = Range("A1") + Range("A2") + Range("A3") Range("A4") = YTDtotal End Sub This is a VBA procedure that I have named YTDcalc. It is quite simple but I will explain. In the procedure I have defined YTDtotal. This will be the sum of a list of values i.e. A1, A2 and A3. This total will then be displayed on the worksheet in cell A4. Ted, you will need to do two things here. (1) Replace A1, A2, and A3 with the cells that you would like to sum on your spreadsheet e.g if you enter payroll figures in B1, F10 and Z100 then change the above code to read: YTDtotal = Range("B1") + Range("F10") + Range("Z100"). (You can have more than 3 range references, I just chose three for ease of explanation) (2) Change the cell reference for where you want the YTDtotal to be displayed on your worksheet. For example if your YTD total appears in cell C20 on your spreadsheet then replace the current code with: Range("C20") = YTDtotal Ok, that is part 1 done. You have set up the VBA code. (you can select <save at this point if you like. [note you can always get rid of this VBA code from your workbook so don't worry about irreversible changes]. Part 2: You can now close the VBE by simply clisking the X in the top-right hand corner. You should now just be looking at your worksheet. Now select <View<Toolbars<Forms. The 'Forms' toolbar appears. Select the 'button' icon (by clicking it - note if you hover your cursor over the icons you get a descriptive of what each one is) and then draw the shape of a button on your worksheet to the size that you want it to be. At this point a dialog box most likely appears with the YTDcalc procedure displayed. Highlight YTDcalc and select <OK. This assigns that VBA procedure to the button so that each time you click the button that VBA procedure runs. You are now almost done. You can close the 'Forms' toolbar and move the button to whichever position you wnat on the worksheet by simply dragging it. Finally, it may be best to give the button a name e.g. 'Calculate YTD Total'. To do this, right-click the button and select <Edit Text and then go right ahead and edit the text (you may need to resize the button if the text is too long). Once that is done, click anywhere on the worksheet and is all is done. Now you can test to see if it works. Enter some new values in your payroll sheet and then click the button to get an answer if the cell that you have designated. By the way, if you want to get fancy then add this at the bottom of your VBA module (between 'Range("A4") = YTDtotal' and 'End Sub'): MsgBox "YTD total = " & Format(YTDtotal, "$0.00") This will display a pop-up box telling you the YTD figure. (To do this simply press ALT + F11 and go back into the VBA module and add the text). Be aware, that when you come to next open this workbook you will get a message asking if you would like to 'Enable Macros'. Please select <Enable Macros as this will allow your VBA procedure to work. Ted, I hope this works for you. Please let me know if you have any difficulties. For your information I am working in a European timezone so possibly we are awake at different times. I will get back to you. Regards Alex "Ted Dawson" wrote: Yes, please elaborate. We're not scared of VBA. "Alex" wrote in message ... Ted As far as I am aware, that is global i.e. applies to all sheets. Just tried it and it seems that way. There is another way around your problem but it involves VBA. Are you familiar with VBA? The solution to your problem would be quite simple and would be sheet specific. Is this of any interest? Alex "Ted Dawson" wrote: Is that setting global, across all the sheets in the workbook, or can it be set for each sheet? |
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Payroll
Alex, works like a champ. Thank you for your help. I certainly appreciate
it. Ted |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
need help with a payroll formula | Excel Worksheet Functions | |||
How do you calculate payroll with tips? | Excel Discussion (Misc queries) | |||
How do I keep each week's data from the payroll calcualtor? | Excel Worksheet Functions | |||
payroll calculator for ontario canada, cant convert ontario rates. | Excel Discussion (Misc queries) | |||
payroll calculator for ontario canada, cant convert ontario rates. | Excel Discussion (Misc queries) |