Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mixed variable problem
Hello,
I have a WS that looks like this A B C 1 Net Sales: 1,411,676 Cost % 2 Salaries: 52,800 3.74% Basically C2 is equal to what percentage B2 is of B1. B2= B1 * C2 C2= B2 / B1 I need to design a worksheet where you can change eitehr the cost(B1) or the percentage(C1). Is there a way to do this with a logic statement somehow? I would appreciat any suggestions. Thank you |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Mixed variable problem
You would need to use a macro to do it. A cell can contain a formula or a
value, but not both. Here is some code I posted for Fwday who, strangely enough, had a problem very similar to yours: 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 -- Regards, Tom Ogilvy "Tom" wrote in message ... Hello, I have a WS that looks like this A B C 1 Net Sales: 1,411,676 Cost % 2 Salaries: 52,800 3.74% Basically C2 is equal to what percentage B2 is of B1. B2= B1 * C2 C2= B2 / B1 I need to design a worksheet where you can change eitehr the cost(B1) or the percentage(C1). Is there a way to do this with a logic statement somehow? I would appreciat any suggestions. Thank you |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
legend problem in mixed line-scatter graph Excel 2007 | Charts and Charting in Excel | |||
Multiple Variable Count problem | Excel Worksheet Functions | |||
Silly Problem with Variable (2nd attempt) | Excel Discussion (Misc queries) | |||
Problem trying to us a range variable as an array variable | Excel Programming | |||
Problem with copying variable(s) to cell(s) and converting strings to mixed case | Excel Programming |