|
|
|
|
Quote:
Originally Posted by cjennings
That did the trick! You certainly know your stuff!
--
Thanks!
"Rick Rothstein" wrote:
Just a quick follow up to both my posting and Jacob's posting...
Using my method, the value in I8 will remain a real number that you can do
calculations with whereas using Jacob's method makes the value in I8 simple
text, so it would not be a number any more.
--
Rick (MVP - Excel)
"Rick Rothstein" wrote in message
...
You can have a defined cell format that is dependent on a variable;
however, if I understood your request correctly, you can do what you are
asking for with some VBA code though. Give this a try...
Right click the tab at the bottom of your worksheet and then click View
Code from the popup menu that appears and then copy/paste the following
code into the code window that opened up...
Private Sub Worksheet_Change(ByVal Target As Range)
Const FormulaCell As String = "J8"
If Target.Address = "$I$8" Then
Range(FormulaCell).NumberFormat = "# " & String(Len(Target.Value), _
"?") & "/" & Target.Value
End If
End Sub
Next, change the example J8 cell reference I used in my code above to the
actual address of the cell that contains the formula you posted. Okay, now
go back to the worksheet and enter a value in cell I8... you need to do
this one time in order for the first format to be assigned (everything
will be automatic thereafter), so you can just type the number that is
already in there over again if you want. Now, whenever you change the
value in I8, the format in the cell you changed my example J8 reference to
will change to a Fraction format with the denominator being the value in
I8.
--
Rick (MVP - Excel)
"fractionwoes" wrote in message
...
I am trying to set up a depreciation schedule and I want the denominator
of
fraction to stay a constant - do not round or reduce. For instance, if
the
months, cell I8, (the denominator) is 72, then the formula
=IF(F13="","",((F$5-F12)/I$8)) would give the answer of 2/72, 3/72, 4/72,
etc. But the denominator won't always be 72, it might be 150, etc. I
have
tried to format the cell to # ???/???, but Excel keeps reducing the
fraction.
|
The best way I have found to do this is to click in the cell in which you want to keep the "x/72" format. Go to the "Format" menu and click "Cells". Choose "Fractions" fro the categories list and then click on any of the choices under the "Type" menu that have "As" in front of them, such as "As halves (1/2)" and click OK. You then need to go back to "Format" and "Cells" and now choose "Custom" from the Categories list. Change the type from "# ?/2" to "# ?/72" and this will force Excel to reduce the fraction to a denominator of 72.
|