Changing consistent Cells

[fwday]

Below you will see a posting the I put in the Misc. group. I haven't
had any luck. As stated below I am making a program for budgeting
numbers. Sometimes you want to budget by percent and sometimes by
Dollars. Using 3 cells. I got a code that I used.
(worksheet_change) It worked great for the 3 cells I used. Now I need
to add multiple cells in the came sheet change. I will be using the
same cell to be basing my calculation on, its just the dollor cell and
the percent cell that are going to change locations. How do I input
this multiple times on the same worksheet_change?

fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
making a cell fixed number to a input number
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)


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-18-2003 07:39 AM



Don Guillett
Guest

Registered: Not Yet
Location:
Posts: N/A
Re: making a cell fixed number to a input number
And how do you tell .20 (%) from .20 (cents)

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)

Report this post to a moderator | IP: Logged

10-18-2003 07:55 AM



fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
fixed Cells
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.


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-18-2003 01:02 PM



RagDyer
Guest

Registered: Not Yet
Location:
Posts: N/A
Re: making a cell fixed number to a input number
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.



------------------------------------------------
View and post usenet messages directly from
http://www.ExcelForum.com/






Report this post to a moderator | IP: Logged

10-18-2003 02:40 PM



fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
Attachment
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.

Attachment: example.xls
This has been downloaded 4 time(s).


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-18-2003 04:44 PM



Tom Ogilvy
Guest

Registered: Not Yet
Location:
Posts: N/A
Re: making a cell fixed number to a input number
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

--
Regards,
Tom Ogilvy

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)

Report this post to a moderator | IP: Logged

10-18-2003 05:55 PM



fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
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


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-19-2003 03:01 PM



Tom Ogilvy
Guest

Registered: Not Yet
Location:
Posts: N/A
Re: making a cell fixed number to a input number
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.

--
Regards,
Tom Ogilvy

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

Report this post to a moderator | IP: Logged

10-19-2003 05:25 PM



fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
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.


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-19-2003 07:24 PM



fwday
Junior Member

Registered: Oct 2003
Location:
Posts: 16
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.


__________________
Thank you for your help



Report this post to a moderator | IP: Logged

10-21-2003 07:48 PM

File Attached: http://www.excelforum.com/attachment.php?postid=325599 (example.xls)

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo Errhandler
If Target.Count > 1 Then Exit Sub
If Target.Column = 2 Or Target.Column = 3 Then

If Len(Trim(Target.Value)) = 0 Then Exit Sub
Application.EnableEvents = False
If Target.Column = 2 And Target.Row Mod 2 = 0 Then
If IsNumeric(Target.Offset(-1, 0)) And _
Not IsEmpty(Target.Offset(-1, 0)) Then
If Target.Offset(-1, 0).Value <> 0 Then
Target.Offset(0, 1).Value = _
Target / Target.Offset(-1, 0)
End If
End If
ElseIf Target.Column = 3 And Target.Row Mod 2 = 0 Then
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

This assumes your wages row is an even row and your entry will either be in
column B (dollars) or in Column C (percent).

The Sales row will always be an odd row.

So it works on all cells in columns B and C