Interdependent Cells; How to?

can you post an example with made up data? I am not exactly understanding what you want to do. Cells cannot contain both a formula and a value.

As Wayne notes, a cell can contain an entered value, or it can contain a formula (and display the value calculated by that formula). If you enter a value into a cell containig a formula, the entered value replaces the formula.

If I'm understanding the description, you want to enter a recurring expense once on one table, and have it appear each day, week or month on a second table.

Creating such a pair of tables is certainly possible.

Regards,

Barry

Mona,

Since cells may only contain either a value or a formula you cannot do what you want in the example. There are actually two reasons:

1) is the reason I have already stated.. value or formula

2) cells cannot have a circular reference. that is:

Cell-Y = Cell-M*12

and

Cell-M = Cell-Y/12

If I replace Cell-Y in the second equation I get

Cell-M = (Cell-M*12)/12

which now has Cell-M dependent on the value in Cell-M

Hi Mona,

Your presumption is correct.

But you could use two pairs of cells, one pair for each direction. This would provide the conversion, but wouldn't be of much use if you intended to use those values in further calculations for two reasons: 1) the formulas doing the 'further calculations' would need to know which of the cells to grab the results from, and 2) Entering new data in the entry cell of either of the cell pairs would chnge the value in the result cell of that pair (and of all cells depending on that result.

You could also do conversions in both directions using four cells, two of which would contain the units to and from which you are converting, This would require a lookup table containing the conversion factors (both directions) for all the possible unit pairs.

A more detailed description of what result you are trying to achieve might make it easier to determine how that result might be arrived at.

Regards,

Barry

You could do something like this:

A3=IF(B1, "YEARS", "MONTHS")

B3=IF(B1,"MONTHS", "YEARS")

B4=A4×IF(B1, 12,1÷12)

B3 is where you enter a number

The notation I am using is, for instance, A3=IF(B1, "YEARS", "MONTHS") means:

in cell A3 type of copy and paste "=IF(B1, "YEARS", "MONTHS")" without the first an last double quotes

uncheck the checkbox to change from YEARS -> MONTHS to "MONTHS -> YEARS"

"...and hypothetically, as soon as you imput a number value into either of the cells (Y or M) then the corespoinding variable cell becomes filled, and therefor the other cell (M or Y, respectively) becomes filled correctly."

Yes it is possible, but there are a couple of issues with it.

With formulas in all four cells, a circular reference is created, and all four cells will show error triangles with this message:

and any cell using the results in any of these cells will also show an error triangle with a message saying "Cell xx contains an error."Once the number has been entered into either of the M, Y cells, the error triangles will disappear, and the results will be shown in the other three cells:

The table shows three sets of Y, M cells and their corresponding V and V2 cells.The top set contains formulas in all four cells (and the error triangles mentioned above).In the second and third sets, a number has been entered in the green-filled cell, and the other three cells show the calculated results.

The error message will lengthen the process of entering data in either of cells M or Y.

Where a cell contains a formula that is not throwing an error, replacing that formula with a number requires--typing the number
--pressing return or tab.Where the formula is throwing an error you must:--click the cell to select it and show the error message.--pause--click a second time to place the insertion point in the cell--type the number--press return or tab (or otherwise exit the cell)OR--double-click the cell to open the equation editor--press delete repeatedly to remove the formula, one character at a time--type the number--press return or tab or click the green checkmark

Entering a number into either the M or Y cell replaces the formula in that cell, a replacement that is not easily reversible (unless you have stored a copy of that formula—and the one in the other of the M and Y cells—to use as a replacement).

It's a 'playable' game, but not, IMO, worth the candle.

Regards,

Barry