ARRRGGH - need help with a formula

  • Thread starter Thread starter sam
  • Start date Start date
S

sam

I need to type a number into a cell and have it show another number in the
same cell. In other words, I want each cell to calculate 60% of whatever
number I insert into it. I am using this to calculate my commission on sales
for each account I have.

Did I mention ARRRGGH?
 
Normally, you would put that calculated value in another cell (probably next
to the one with your sales amount in it. If you don't do that, you will have
no "record" of your sales (which means they can't be searched for any
discovered errors). Anyway, if you decide to do this (use a separate column
for your commission), then just put this formula in the cell adjacent to
your sales figure (which I'll assume to be Column A since you didn't say)...

=0.6*A1

If, on the other hand, you want to continue with your original idea and have
the figure change when entered in, you will have to use a worksheet event to
accomplish this (since a single cell cannot have a formula in it and also
take typed in values for that formula to act on). Right-click the tab at the
bottom of the sheet you want this functionality on and select View Code from
the menu that pops up. Now, copy/paste the following code into the code
window that appeared...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If Target.Column = 1 Then
Application.EnableEvents = False
Target.Value = 0.6 * Target.Value
End If
Whoops:
Application.EnableEvents = True
End Sub

And, again, I have assumed Column A (hence the 1 in the If-Then test) as the
column where you want this functionality.

Rick
 
Hi Sam,

Why not use a separate column for the values to be typed in and it is then
easy to create a formula for the one to display. The separate column can be
hidden when you finish.
 
WOW! Amazing. I tried it and it works. By the way, I have tracking via
billing sheets I turn in. But I want to make sure my paychecks reflect the
60% in an easy way. Thus the input in this fashion.

Hey, can I do this for multiple columns? My first column is 7 (Jan) and
then extends to 9, 11, 13, 15, etc all the way through Dec.

I tried some variations on the code, but couldn't get the multiple columns
to compute.

Thanks for any more help.
 
The code is limiting the feature via the If-Then statement. We can either
eliminate it and let the feature apply to any number typed in anywhere on
the sheet or, if there are numbers you will be typing in, then I need an
exact rule that describes which columns to apply it to. Looking at your 7,
9, 11, 15, etc. statement, one might conclude that it applies to every other
column starting with Column G (7th column). Is that the rule? Does it apply
across months? Or does it reset to the first of each month even if that is
not an "every other column"? If you can tell me what the columns are, or a
general rule for figuring them out, I'll be happy to modify the code to
account for it.

Rick
 
Thanks for the help.

I have frozen row 1 and columns A-F. Across row one are the titles for
months Jan-Dec starting in column G and occurring every other column, ending
with column AC.

In Column A extending from row 1 to row 157, I have the facilities listed
that I currently service. The facilities have three rows that separate them.
This list of facilities will expand and contract due to adding and/or losing
clients to service. Some of the names are long enough to extend to column F.

As I have access on my laptop to the billing for each facility for each
month, I simply want to enter that amount into the appropriate cell and have
it compute my commission. Sometimes the commissions come in over two checks
over several different pay periods. So, I need an "easy" way to confirm my
commission is correct. (I realize I could simply add another column to each
month and create a very simple formula to compute the commission to an
adjacent column. Sheesh - probably should have. But, I didn't want to have
the spreadsheet that wide.)

All this to say that columns 7, 9, 11, 13, 15, 17, 19, 21, 23, 25, 27, 29
represent months Jan through Dec and need to compute at 60%. There are
adjacent columns to each of those to the right that contain a fuel charge
that I receive 100% of. So, I don't need those to compute. Just every other
column starting with 7 and ending with 29.

I hope that helps. Thanks a bunch for yours.

sam
 
Okay, try this code in place of the code I gave you earlier...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Whoops
If InStr("*7*9*11*13*15*17*19*21*23*25*29*", "*" & _
Target.Column & "*") And Target.Row > 1 Then
Application.EnableEvents = False
Target.Value = 0.6 * Target.Value
End If
Whoops:
Application.EnableEvents = True
End Sub

Rick
 
Looks good so far. Will look at it more later when I have more time. Thanks
SO much for your unselfish help. I'll write back if I have a problem.

sam
 
Back
Top