making a cell fixed number to a input number

  • Thread starter Thread starter fwday
  • Start date Start date
F

fwday

I am doing a caclculation. Some of the time I want to do it with a
percentage and sometimes with a dollar amount. If I punch in a dollar
amount I want it to come up with the persentage. If I type in the
percentage I want it to come up with the dollar amount. How can I make
the formula stay in a cell even if I punch something in.

(e-mail address removed)
 
The Dollars and the Percents are 2 diffrent cells. Its just that
sometimes I want to put in a percent and sometimes a dollar. For
example. in cell A1 is a dollar amount. In the Cell A2 is the dollar
amount of the pecentage of Cell B2. If I cange B2 it will change the
dollar amount. Then if I go back to A2 and put in a dollar amount the
formula for the equasion will be gone. I don't want it be be gone I
just want it to be in the background and change the percent amount.
 
I think I understand what you're talking about.

You have a formula in A2 that calculates a percent of B2 ?
Maybe =B2*25%
OR
=B2*0.25

You perhaps want the results of this formula to match the dollar amount that
is in A1 ?
You also want to know what percent of B2 it takes to match the dollar amount
in A1 ?

If this is what you wish, try these ideas:

Change the formula in A2 to:
=B2*C1
Where YOU put a percent in C1 (decimal 0.25), and change it around until you
get the dollar amount that you want,
OR,
Change the formula in A2 to:
=B2*A1/B2
Where you automatically get the dollar amount in B2 to match the dollar
amount in A1,
(Don't know what this would accomplish)
OR,
Enter this formula anywhere:
=A1/B2%
Where you will get the percent A1 is of B2.

Of course, if I read this wrong, and you want the percent of A1 to match B2,
interchange A1 and B2 in the above formulas.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


The Dollars and the Percents are 2 diffrent cells. Its just that
sometimes I want to put in a percent and sometimes a dollar. For
example. in cell A1 is a dollar amount. In the Cell A2 is the dollar
amount of the pecentage of Cell B2. If I cange B2 it will change the
dollar amount. Then if I go back to A2 and put in a dollar amount the
formula for the equasion will be gone. I don't want it be be gone I
just want it to be in the background and change the percent amount.
 
I am sending an attachment so that you may understand what I am looking
for. I am making a worksheet to budget off of. Sometimes you budget
by dollars and sometimes you budget by percent. B7 is the sales. B8
is the Wages. And C8 is the percent of wages. I want to have the
option to punch in the percent of wages that I am looking for or the
dollars of wages. If I make a typed change in cell B8 wages I want the
C8 percent to change. If I punch in the C8 percnet I want the wages to
change. This will give the flexiblity to budget eather way.

File Attached: http://www.excelforum.com/attachment.php?postid=319376 (example.xls)
 
Right click on the sheet tab and select view code

paste in code like this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B8:C8")) Is Nothing Then
If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Address = "$B$8" Then
Debug.Print 1
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
Debug.Print 2
If Target.Offset(-1, 0).Value <> 0 Then
Debug.Print 3
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
Else
If IsNumeric(Target) Then
Target.Offset(0, -1) = Target.Offset(-1, -1) * _
Target
End If
End If
End If
Errhandler:
Application.EnableEvents = True
End Sub
 
That worked great. Now I can't get it to do it on more then one set of
cells. I am doing a budgeting program And I am going to have to do
this several times on one page. I keep getting a Compile error:
Ambiguous name detected: Worksheet_Change
 
You can only have one worksheet_change macro. You would need to make the one
macro work with all the cells where you want that behavior.
 
How do I do that. Could you give me an example of more the one group of
cells. I should be able then to carry it on to all the ones I need.
 
There is a copy of a sheet_change in this posting. I am looking on how
to make multiple sheet_changes on one page. Please see example above
and let me know.
 
Back
Top