format a cell when I type 10 it will multiply 10 by .585
 

I'm trying to format a cell in a way where the number I input will
automatically be multiplied by a number predetermined by me.
ie: I format cell a1 so it's contents is multiplied by .585
when I input the number 10 in cell a1 it will display as 5.85

format a cell when I type 10 it will multiply 10 by .585
 

Hi,

You could do a couple of things. First a macro. right click your sheet tab,
view code and paste this in. Change A1 to whatever you want and D1 to the
cell to keep your constant in

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" And IsNumeric(Target) Then
Application.EnableEvents = False
Target.Value = Target.Value * Range("D1")
Application.EnableEvents = True
End If
End Sub

you could also do this in say b1
=A1*0.585
Now whenever anything is entered into a1 it is multiplied by your constant.
Note the constant could also be a cell address.

To extend this you could use this formula
=OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1)*0.585
Put this in any cell and if you enter a number in the cell to the left of
that cell it gets multiplied by your constant.

Mike


"Mikeebabe" wrote:

I'm trying to format a cell in a way where the number I input will
automatically be multiplied by a number predetermined by me.
ie: I format cell a1 so it's contents is multiplied by .585
when I input the number 10 in cell a1 it will display as 5.85

format a cell when I type 10 it will multiply 10 by .585
 

You can't do this with format. While you can do it with a macro, you're
almost always better off to use another cell or column.

Put your multiplier in, say, A1, data in B1, then your formula is:
=$a$1*b1

Now your multiplier is easy to change without re-entering data, and errors
are easy to fix.

Regards,
Fred.

"Mikeebabe" wrote in message
...
I'm trying to format a cell in a way where the number I input will
automatically be multiplied by a number predetermined by me.
ie: I format cell a1 so it's contents is multiplied by .585
when I input the number 10 in cell a1 it will display as 5.85

format a cell when I type 10 it will multiply 10 by .585
 

I would highly recommend you not do this.

Use an input cell then use a formula to do the calculation in another cell.

A1 = input cell
B1 = formula

=IF(COUNT(A1),A1*0.585,"")

If you do it the way you want and you make a mistake how will you know? You
won't unless it's very obvious.


--
Biff
Microsoft Excel MVP


"Mikeebabe" wrote in message
...
I'm trying to format a cell in a way where the number I input will
automatically be multiplied by a number predetermined by me.
ie: I format cell a1 so it's contents is multiplied by .585
when I input the number 10 in cell a1 it will display as 5.85

Hi Mike H,

I have a similar question to the one above but I want to know how to apply the code to a range of cells rather than just one code.
ie. I want all of the cells in columns F-H to be multiplied by some constant (for example 1000).

Any suggestions?

Thanks