0

I would like cell M6 to add $12.73 every time I add a number greater than 0 to M7.

Example:

I am doing payroll. For every day worked (Field M7) I bill $12.73 (Field M6). I do this every month, and need to keep a running list.

Instead of always having to put the number of days in field M7 and then add $12.73 to field M6, I would like the +$12.73 to compute automatically when I add the number of days to M7.

Please advise.

Thanks in advance.

WGS
  • 13,969
  • 4
  • 48
  • 51
user3385034
  • 1
  • 1
  • Thanks for your quick response.How would M6 know to add $12.73 to its cell, when I make changes to M7? I'm not looking to multiply M7 by cell M6, but rather add $12.73 to M6 every time I make a change M7. Please advise. – user3385034 Mar 05 '14 at 18:59
  • 1
    in that case look into VBA (`Worksheet_Change` event) – Dmitry Pavliv Mar 05 '14 at 18:59
  • Going with @simoco on this one. VBA is pretty much the only way to do this, since oddly enough you're not multiplying them (maybe you have a base value?). However, caution on the requirement *everytime I make a change to M7* as that means a different thing in `Worksheet_Change` events. Simply editing the cell will trigger another $12.73 to be added. – WGS Mar 05 '14 at 19:04
  • Thanks. How would I write the formula to trigger the cell to add $12.73 when I change another cell? – user3385034 Mar 05 '14 at 19:30

2 Answers2

0

This couldn't be done with formula to the best of my knowledge. You could hypothetically add another cell which you manually increment every time you do something but a more elegant solution would be to use VBA to automatically increment the value of the cell by 12.73.

Take a look at this similar question. Check updates automatically

try this in the sheet that you want, the sheet object "me" will only work if the code is contained there and not in a module. As a side this will also add 1 to the value when the cell is cleared, but it should be enough to get you started:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A5")) Is Nothing Then Me.Range("B5").Value = Me.Range("B5").Value + 1

End Sub

Community
  • 1
  • 1
sten
  • 380
  • 1
  • 5
  • 14
0

Put this formula into M6:
=if(M7>0,"12.73","")

Enter your days worked into M7, and M6 will automatically populate 12.73.

Sᴀᴍ Onᴇᴌᴀ
  • 8,218
  • 8
  • 36
  • 58
Jen
  • 1