#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Gary''s Student
 
Posts: n/a
Default HTH

First enter your three initial values
Then copy this small macro to worksheet code:

Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Range("B1:C1"), Target) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Intersect(Range("B1"), Target) Is Nothing Then
Range("B1").Value = Range("A1").Value * Range("C1").Value
Else
Range("C1").Value = Range("B1").Value / Range("A1").Value
End If
Application.EnableEvents = True
End Sub

If you change B1, then C1 will automatically update.
If you change C1, then B1 will automatically update.

If you change A1, then nothing will automatically update.
--
Gary's Student


"Abhi" wrote:

I have a formula with circular reference. When a I put any valur in
any of these reference cells the formaula gets overwritten by the
value. Is there any way that I can put values in any of these cells
without overwriting the formuls to try various permutations. Below is
the example:-

Cell A B C
500 10 2%
Formula +C1*A1 B1/A1

Cell B1 & C1 are having formula with cicular reference. What I am
trying to get is that if I change the B1 value to 50 from 10, then C1
should automatically change to 10% & vice versa. OR If C1 is changed
to 10% B1 should change to 50 so that I can try as many combinationas
possible.

The problem here is that once i put "50" in B1 or "10%" in C1 the
formula gets overwritten with the absolute value.

Would be thank ful if anybody can suggest any solution for this?

Regards
Abhi


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 10:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"