MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Circular Reference

Posted by Brian on January 04, 2002 1:44 PM

How can I, without adding another column convert number of overtime hours worked and multiply by 1.5. As an example in A1 I input 8 and it converts to 12.
Thanks in advance and Happy New Year!!!

Posted by Scott on January 04, 2002 1:51 PM

You can put 1.5 in a blank cell, copy it, and then paste-special using multiply over your entries.

Posted by Adam S. -nt on January 04, 2002 1:51 PM

type 1.5 in a cell\copy\highlight data\paste special\values

Posted by sorry 'bout that. Adam S. on January 04, 2002 1:53 PM

erm..that's paste special\multiply :P

Posted by Scott on January 04, 2002 1:53 PM

Don't forget to hit Multiply!!!

Posted by Jacob on January 04, 2002 1:57 PM


Assuming all OT is in Column A Then try this:
Put this code on the sheet code that you want it to trigger for.

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 Then 'This will check Column A only
Application.EnableEvents = False 'Turn off events so it wont recalculate forever
Target.Value = Target.Value * 1.5
Application.EnableEvents = True 'Turn events back on so it will check on the next change
End If

End Sub



Posted by Brian on January 04, 2002 2:49 PM

Jacob, Adam, and Scott

Aloha Guys,

Thanks for the quick response!

Jacob, I'm a newbie at Macros and would like to avoid using it, although it does work. This worksheet is a shared folder used by many.

Adam and Scott, Is there a way I can formulate each cell (without using a macro) that when the user enters 8 it automatically inputs 12 after leaving the cell?


Posted by Scott on January 04, 2002 3:07 PM

Re: Jacob, Adam, and Scott

Brian, Without Vb, I'm not sure this can be done; although there are a lot of people on this board with amazing talents, and maybe they know something. If so, I'd love to learn too.....