Results 1 to 9 of 9

20050510, 21:43 #1
 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

20050510, 21:50 #2
 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

20050510, 22:45 #3
 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

20050511, 00:15 #4
 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

20050511, 01:30 #5
 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.

20050511, 01:49 #6
 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 vlook up for the percentage number. But this " cannot exceed 100%" policy should always be met, how can I avoid the "vlook 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

20050511, 15:54 #7
 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 nonzero 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 nonzero 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

20050511, 16:49 #8
 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

20050511, 20:21 #9
 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