# Thread: Help with Spreadsheet formulas (2002)

1. ## Help with Spreadsheet formulas (2002)

I want to add the COMMISSION THIS PERIOD to the LAST TOTAL and have it show the YTD COMMISSION & TOTAL. The problem arises when it's the next period. What do I do to have it use the YTD COMMISSION & TOTAL amount calculated previously to be the LAST TOTAL ? I've attached a portion of the spreadsheet to this post.

I will appreciate any help or advice for this project. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

2. ## Re: Help with Spreadsheet formulas (2002)

I don't understand and you did not attach the spreadsheet.

Make sure it is <100k, and you attach it AFTER you preview and AFTER you spell check
Steve

3. ## Re: Help with Spreadsheet formulas (2002)

Let me try this again. I have three columns - PREVIOUS TOTAL COMMISSION, COMMISSION THIS PERIOD and YTD COMMISSION. I add the PREVIOUS TOTAL COMMISSION to COMMISSION THIS PERIOD to get the YTD COMMISSION. The problem arises when I want to calculate the next period. I type in a new COMMISSION THIS PERIOD. I want the YTD COMMISSION to become the PREVIOUS TOTAL COMMISSION and then add the two to arrive at a new YTD COMMISSION.

4. ## Re: Help with Spreadsheet formulas (2002)

You must use a macro. Add this to the sheet object of the sheet of interest. If you make any changes to the cells in the range C3:C12 (change as appropriate) the VALUE of column B in that row will become the VALUE in column D of that row. The formula in D remains. Note: you must copy the formula from D3 to D412.

Steve

<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
If Not Intersect(Target, Range("C3:C12")) Is Nothing Then
For Each rCell In Intersect(Target, Range("C3:C12"))
rCell.Offset(0, -1).Value = rCell.Offset(0, 1).Value
Next
End If
End Sub</pre>

5. ## Re: Help with Spreadsheet formulas (2002)

Is there another way around this? I don't know how to use macros. Or, is there a place on the internet to learn how? Thanks.

6. ## Re: Help with Spreadsheet formulas (2002)

The alternative:
Before you add the value in column C:
You can manually copy the value the column D and paste-special the value to column B
Then change the value of column C

To use the macro:
Open VB (Alt-F11)
In the "explorer pane" on the left: find the VBAProject (WorkbookName) [where "workbookname is your file name of interest]
dbl-click the "microsoft Excel object" that is named the sheet name of that page
Copy the code I attached in my previous note
PASTE the code into the right hand pane in the VB editor
close VB (alt-q)

The macro runs automatically. You only have to worry about it, if you want more rows of column C to do it or you work with other columns than B and D.

Steve

7. ## Re: Help with Spreadsheet formulas (2002)

Here on the Lounge is the best place to learn about Excel Macros. If you also look at these, you'll find some resources:

<!mskb=304494>Microsoft Knowledge Base Article 304494<!/mskb>
Excel MVP's
MVP Word beginner macro tutorial Yes, it's for Word, but it will teach you about the VBE, etc. - scroll down the page to "Creating a macro with no programming experience using the recorder".
Google Search for Excel Macro
Google Search for Excel Visual Basic

8. ## Re: Help with Spreadsheet formulas (2002)

I can't seem to make it work. When I paste your code into the right hand pane in the VB editor, it is all on one line and doesn't wrap. However, I have had to change the worksheet and am attaching the new worksheet. I placed comments in the column headers to explain what is supposed to happen. Basically, there is a commission this period that needs to get added to the year to date commission. Then there is a design fee this period that needs to get added to the YTD design fee. Then the YTD design fee and the YTD commission get added together for the YTD gross. I am attaching the worksheet and I have gone in to the VB and added the code so you can see how it looks. Although, with the change in the worksheet, it probably won't work but I wanted you to see what happens when I paste it in. Could you explain what themacro is doing?

The person who originally posted this (on my behalf) was trying to help me. She changed the columns from my original sheet . I'm such a novice at this but she may have changed it because it can't be accomplished in the way I have set up the sheet (as attached). Can it work this way, or do I have to make an additional column for "previous total commission"? I hope I'm not being too confusing here. I know what I mean - I just don't know if I'm saying it correctly. But I am ever so grateful for your help!

9. ## Re: Help with Spreadsheet formulas (2002)

Thank you, John. I really appreciate the resources and am quickly learning to appreciate you folks here on the Lounge! Again, many thanks.

10. ## Re: Help with Spreadsheet formulas (2002)

Try it as follows:

1.Copy Steve's code into Word
2. Find and Replace all the Ranges in Steve's code from "C3:C12" to "C4:C13"
3. Right click the sheet name, select "View Code"
4. Copy the code from Word, paste into the VB window

See if it works, post back if not.

(We could do this for you, but where's the fun for you?)

11. ## Re: Help with Spreadsheet formulas (2002)

I changed the macro. It now looks for changes in E4:F13 and when you make a change to cells in either column it adds the new value to (respectively) column C and D.

I attached a new workbook with the working macro. Is this what you want?

Steve
<pre>Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim rCell As Range
If Not Intersect(Target, Range("E4:F13")) Is Nothing Then
For Each rCell In Intersect(Target, Range("E4:F13"))
rCell.Offset(0, -2).Value = rCell.Offset(0, -2).Value _
+ rCell.Value
Next
End If
End Sub</pre>

12. ## Re: Help with Spreadsheet formulas (2002)

There was more change since she rearranged the columns. I added it to the workbook just to get it working.

Steve

13. ## Re: Help with Spreadsheet formulas (2002)

Ok, John, I'm having lots of fun! <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> You're right, it's a great way to learn. So, I did it! However, this still isn't what I need. Yes, it works to update the year to date commission. However, the problem arises next pay period. Let's say everyone gets \$2,000 commission this pay period. I insert the \$2,000 in each row of column E. It updates the YTD commission and the YTD gross just like its supposed to. <font color=ff8c00>Now, its the next pay period</font color=ff8c00> . I open the sheet and need to insert new numbers in the commission this period field. When I do this, all the other fields get messed up because it deletes the \$2,000 from last period and then adds whatever I put in the field. I need it to keep the updated YTD commission field, then add whatever new I put in the commission this period field.

You have the patience of a saint!

14. ## Re: Help with Spreadsheet formulas (2002)

woo hoo! it works! I'm sooooo happy! I don't understand why it didn't work when I did the copy and paste like John said. I know that the range changed in the worksheet, but I was only trying it with the commission ... oh, never mind, I get it now. Now, for the macro. What is it doing?

15. ## Re: Help with Spreadsheet formulas (2002)

I understand this part:

It now looks for changes in E4:F13 and when you make a change to cells in either column it adds the new value to (respectively) column C and D.

But what part of the macro is telling it to leave what is already in C and D and add the new value to them?

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•