"Two-way" Formulas?

[Enigman O'Maly]

I have a spreadsheet that I use to help me make investment alocation
decisions, i.e.,


A B C
+------+------+------+
1 |10000 | 45% | 4500 | <- C1 formula = A1*B1
+------+------+------+
2 | | 15% | 1500 | <- C2 formula = A1*B2
+------+------+------+

This works fine if I'm adjusting percentages (col B) to get amounts (col
C).

But sometimes I would like to enter amounts (col C) and have the percents
in col B adjusted.

How can Excel be told to do such "two-way" formulas?

Many thanks for suggestions...

 

[Paul]

I have a spreadsheet that I use to help me make investment alocation
decisions, i.e.,


A B C
+------+------+------+
1 |10000 | 45% | 4500 | <- C1 formula = A1*B1
+------+------+------+
2 | | 15% | 1500 | <- C2 formula = A1*B2
+------+------+------+

This works fine if I'm adjusting percentages (col B) to get amounts (col
C).

But sometimes I would like to enter amounts (col C) and have the percents
in col B adjusted.

How can Excel be told to do such "two-way" formulas?

Many thanks for suggestions...

It can't.
A cell can contain either data or a formula, not both.
Why not splash out and use another six cells?

 

[Andy Wiggins]

It can be done, but you will need to use some VBA programming involving the
SheetSelectionChange event.

This file might be a help to you:
http://www.bygsoftware.com/examples/zipfiles/OverwriteAndRetainFormula.zip

It's in the "Worksheets with VBA" section on page:
http://www.bygsoftware.com/examples/examples.htm
It demonstrates how user input is captured and used to amend a formula with
the "SheetSelectionChange" event.

The code is open and commented.


--

Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"