"Two-way" Formulas?

  • Thread starter Thread starter Enigman O'Maly
  • Start date Start date
E

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...
 
Enigman O'Maly said:
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...
Click to expand...

It can't.
A cell can contain either data or a formula, not both.
Why not splash out and use another six cells?
 
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"
 
Back
Top