switch between formula and input

[SRS22]

Is it possible to write a VBA code where if I enter a 2 in cell A2 then cell A3 is calculated as A1*A2 but if I enter 6 in cell A3 the A2 is calcullated as A3/A1?


A1 = 3 (constant)
A2 = 2 (enter)
A3 = A1*A2 (formula)

or

A1 = 3 (constant)
A2 = A3/A1 (formula)
A3 = 6 (enter)

There are times when I know the value of A2 and want to calculate A3 and other times I know A3 and want to calculate A2.

[Richard Buttrey]

Hi,

One way with the following sheet change event

Please Login or Register  to view this content.

[SRS22]

I am new at this and am not sute if my reply was posted so I will try again, sorry if it is a repeat.

Can I write this so that A2 and A3 are variables?

[Richard Buttrey]

Hi,

Not quite sure what you mean. By definition A2 & A3 are already variables since you can enter any number you want. If you mean that A2 & A3 could be anywhere then best practice is to assign two Excel range names to them and substitute the names in the macro.

If you mean something else then please clarify

[SRS22]

It appears to me that if I want to change A2 or A3 I will have to change the value in the code. Can the code be written with target value open? He is my application.

I want to size a water tank at at sewer plant, for an example, the plant has an in-flow of 5 mgd so I enter 5 in A1 which is the in-flow input. A portion of the in-flow is recycled back to the front of the plant, say 50% so I enter 50% in A2. A3 is the total flow so it's formula is A1+(A1*A2) or A1*A2 if I enter 150% in A2. However there are times when I know the in-flow A1 and I know the total flow A3 and want to solve for the % of recycle flow A2. I was hoping that I could write a code that depending on which cell I entered a value in, A2 or A3, the other would calculate a result.

Not sute if I made it clearer or more confusing

[Richard Buttrey]

That helps. Your original post mentioned adding the value 2 in A2 or 6 in A3.
I took you literally. It appears that these are just two constants, like A1 is a constant.'

In which case just remove the AND comparisons in the macro. i.e. the new code is now just

Please Login or Register  to view this content.

If the entry cells may not be A1:A3, but somewhere else then best practice is to name the cell(s) range. So for instance name A1 'Inflow', A2 'Recycle' & A3 'Total'. Then the macro is

Please Login or Register  to view this content.

[SRS22]

That did it! Thanks for your help