Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    New Lounger
    Join Date
    Jun 2002
    Location
    San Leandro, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #3
    New Lounger
    Join Date
    Jun 2002
    Location
    San Leandro, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #9
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    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?)
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #14
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    New Lounger
    Join Date
    Sep 2003
    Location
    Emerald Hills, California, USA
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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 LastLast

Posting Permissions

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