Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to set a conditional format that is dependent on cell type | Excel Discussion (Misc queries) | |||
"If this, multiply by this" type of equation | Excel Worksheet Functions | |||
Adding new 'Type' to Format->Number->Time->Type | Excel Discussion (Misc queries) | |||
Change Excel cell format date type examples ("14-Mar-01") | Setting up and Configuration of Excel | |||
Can I set the numerical type of Cell to Hex format | Excel Discussion (Misc queries) |