Changing one cell or Another

[luisconst]

I have two cells. One with values and another with %. I want to use the Value one and Excel calculates the other with %.

If I change it and use the % cell, I want excel to calculate the Value.

Example:

-A1 = 500

-C1 is Value

-C2 is %

If I use C1 with 50 it will give me C2 = 10%

If I use C2 with 50% it will give me C1 = 250


How can I do this? Please help

 

[ramkrishna]

Try this.

=IF(CELL("format",C1)="P0",A1*C1,TEXT(C1/A1,"0%"))


Sample is uploaded at

https://skydrive.live.com/view.aspx?cid=FC7700310D509404&resid=FC7700310D509404%21136

 

[luisconst]

Thanks,


But it isn't what I meant.

I have a total amount to spend, I could be 500 dollars (A1 = 500)

Than I have two columns, one for % and the other for values (C1 and C2)

If I use the C1 with 10, it will give me 50 in C2.

If I write 100 in C2, it will give me 20% in C1.


I want something similar to this: http://www.convertunits.com/from/kgf/to/kN

It doesn't matter the cell I use. It always give me the answer in the other.


I guess I have to use a Macro, because if I use formulas it would be erased after I use the cells.

 

[vaskov17]

Private Sub Worksheet_Change(ByVal Target As Range)

With Target

If .Address = "$C$1" Then

.Offset(1, 0).Value = Val(.Offset(0, -2).Value) * Val(.Value)

ElseIf .Address = "$C$2" Then

.Offset(-1, 0).Value = Val(.Value) / Val(.Offset(-1, -2).Value)

End If

End With

End Sub

 

[luisconst]

Thanks Vaskov.

I used your code and made some changes to it so the % could work. Now It works perfectly.


Private Sub Worksheet_Change(ByVal Target As Range)

'

Dim Valor As Single

'

With Target

If .Address = "$C$1" Then

Valor = .Offset(0, 0).Value

.Offset(1, 0).Value = Val(.Offset(0, -2).Value) * Valor

ElseIf .Address = "$C$2" Then

Valor = .Offset(0, 0).Value

.Offset(-1, 0).Value = Valor / Val(.Offset(-1, -2).Value)

End If

End With

End Sub


THANKS

 

[vaskov17]

I am not sure how the Valor variable fixes the % problem. What I did was to set the formatting of cell C1 to % and it worked. Sorry I forgot to post it in the original.