Results 1 to 9 of 9
  1. #1
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    fill in some column according to the information i (excel 2000)

    Can I fill in some column according to the information in one specified column? For example, if in column F, the date is 10/01/97, then fill 23%, 20%, 0%, 0%, 0% in column G, H, I, J, K and L accordingly. Column N should be the total percentage of column G, H, I, J, K and L. However, column N cannot exceed 100%. If column N exceed 100%, the last column (either column G, H, I, J, K and L) should adjusted to a number so column N=100%.

    Thanks for your help

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    You need a better deffinition of what you want to do. What do you want to happen if column F contains some other date? Is there some logic to those percentages you want to fill in columns G to L? HOW should the percents in Columns G to L be adjusted if N is more than 100%.
    Legare Coleman

  3. #3
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    Column F will always have the grant date information. There is no logic between column F and column G to L. Column G and L are just the yearly bonus information according to the company performance. If a bonus plan is granted at, say 10/01/97, it always gets 23% bonus in Y98, ect

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    We would need to see at least a little of the table to be able to tell you how to do the first part. It can probably be done using formulas in colunms G to L that ust the VLOOKUP function to look up the percents in the table.

    The second part of your question about fixing the percent if it is greater than 100 can be solved by putting the VBA code below into the Worksheet Calculate event routine for the worksheet. This code goes in the module behind worksheet (Sheet1) in the workbook you uploaded.

    <pre>Private Sub Worksheet_Calculate()
    Dim lRowMax As Long, I As Long, J As Long, K As Long
    Application.EnableEvents = False
    lRowMax = Range("N65536").End(xlUp).Row - 1
    For I = 1 To lRowMax
    If Range("N1").Offset(I, 0).Value > 1 Then
    For J = 5 To 0 Step -1
    If Range("G1").Offset(I, J).Value <> 0 Then
    If J = 0 Then
    Range("G1").Offset(I, 0).Value = 1
    Exit For
    Else
    Range("G1").Offset(I, J).Value = 1
    For K = J - 1 To 0 Step -1
    Range("G1").Offset(I, J).Value = Range("G1").Offset(I, J).Value - _
    Range("G1").Offset(I, K).Value
    Next K
    Exit For
    End If
    End If
    Next J
    End If
    Next I
    Application.EnableEvents = True
    End Sub
    </pre>


    I have attached your workbook with this code included. You can test it by changing the percents so that they add up to more than 100%.
    Legare Coleman

  5. #5
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    Many thanks for your prompt reply. This is what I want to achieve.

  6. #6
    Lounger
    Join Date
    Aug 2004
    Posts
    47
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    I just tested to type a large percentage number in column H(say, 80%), then column I will end up having a nagative number.

    Maybe I didn't make myself clear. I may like to modify one of the columns ( G to L) according to different situation. It may not always be column I ( or column H, or G or J or K or L). For example, if column H is 80%, then I would like to modify this column to 77% and show "n/a" in column I instead of having a nagative number in column I. In another word, I like always test the 100% policy for each column, whenever we find a column and its previous columns' total exceed 100%, I like to modify this column and put "n/a" for the following year(column).

    Another question, I like your tip about doing a v-look up for the percentage number. But this " cannot exceed 100%" policy should always be met, how can I avoid the "v-look up" adding some percentage number to a person's information if this person has already met the 100% policy. In another word, can I lock all the cell that have "n/a" in them? Thanks

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    This is not what you said you wanted to do. You said that if the total was greater than 100%, you wanted to adjust the last non-zero value down so that the total was equal to 100%. That is what my code does. Doing that, if the precents prior to the last non zero percent add up to more than 100%, then the adjustment is going to make the last non-zero value negative. If this is not what you want, then you are going to have to give a better description of what you do want.
    Legare Coleman

  8. #8
    New Lounger
    Join Date
    May 2005
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    You are right. I apologize for the confusing.

    And I realized that the situation I mentioned yesterday actually didn

  9. #9
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: fill in some column according to the information i (excel 2000)

    Changing the J = 5 to J = 6 will add F04 which there is already a column for. However, if you add an F05 and F06, then there are a number of things that will have to change because you will have to add one or two additionl columns which will shift column N to some other column.

    It looks like you changed my code in the line which read:

    <pre> If Range("N1").Offset(I, 0).Value > 1 Then
    </pre>


    It looks like you changed the "N1" to "U1". I can see why you made that change, but you should have also changed the "N65536" to "U65536" in the line which reads:

    <pre> lRowMax = Range("N65536").End(xlUp).Row - 1
    </pre>


    I think the code below will do with columns G to M:

    <pre>Private Sub Worksheet_Calculate()
    Dim lRowMax As Long, I As Long, J As Long, K As Long
    Application.EnableEvents = False
    lRowMax = Range("N65536").End(xlUp).Row - 1
    For I = 1 To lRowMax
    If Range("U1").Offset(I, 0).Value > 1 Then
    For J = 2 To 0 Step -1
    If Range("P1").Offset(I, J).Value <> 0 Then
    If J = 0 Then
    Range("P1").Offset(I, 0).Value = 1 - Range("N1").Offset(I, 0).Value
    Exit For
    Else
    Range("P1").Offset(I, J).Value = 1 - Range("N1").Offset(I, 0).Value
    For K = J - 1 To 0 Step -1
    Range("P1").Offset(I, J).Value = Range("P1").Offset(I, J).Value - _
    Range("P1").Offset(I, K).Value
    Next K
    Exit For
    End If
    End If
    Next J
    End If
    Next I
    Application.EnableEvents = True
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

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