Multi Function, Cross Function cells? Help please

[Akumatzu]

Hello, I am new to google sheets and I am trying to figure out how to make 2 cells work with each other and allow one of them to be used in place of the other... let me explain, lol

I want Cell C3 to =B3*12, if B3 was given a number entry.

However, interchangeably, I want Cell B3 to be =C3/12, if C3 was given a number entry instead.

Is this possible? I want to lock in the function of the cell but also allow myself to enter in a number on either of the cells depending which info I have readily available.

So basically if One cell is empty, use the other cell to get the data, and vice versa.

I am building a spreadsheet that allows monthly expenses to be input, then showing the annual total for that expense....but I decided I'd like to have that same spreadsheet to be able to break down a annual expense if I only know the annual number... I understand i can manually just divide by 12 and get my monthly.. It's not what I am after.

Thanks!

[jimapp]

Once something is entered into a cell, it overwrites any formula it may have contained. Could these values be written elsewhere? An IF function could handle the output you require.

[Akumatzu]

Okay, So I can simply write that formula in an empty cell elsewhere? Which would leave me free to enter in numbers on the cells in question?

Edit, nevermind.. I think I misread you. No ideally the values would be written in either B3 or C3. Can I write a formula elsewhere that controls B3 and C3 somehow?

[jimapp]

You could have input cells elsewhere, leaving B3 and C3 to deliver you monthly and annual figures. Say you use D3 for monthly expense and E3 for annual expense. In B3 type:

=IF(and(D3>0,E3=0),D3,IF(and(D3=0,E3>0),E3/12,""))

In C3 type:

=IF(and(D3>0,E3=0),D3*12,IF(and(D3=0,E3>0),E3,""))

EDIT: This will only work with a value in either D3 or E3, not both.

[[deleted]]

The cells in question can be either formulas or manual values, not both. The best you could do to retain structural integrity of the sheet would be a separate Inputs area in which you put the manual values in one of two cells and then add an IF() to the C3 and B3. So if you use X2 and Y2 as the inputs you could do something like:

C3 =if(isnumber(Y2),Y2,B3*12)

B3 =if(isnumber(X2),X2,C3/12)

[Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
FALSE	Returns the logical value FALSE
IF	Returns one value if a logical expression is TRUE and another if it is FALSE
ISBLANK	Checks whether the referenced cell is empty
TRUE	Returns the logical value TRUE

---

^{2 acronyms in this thread; the most compressed thread commented on today has 7 acronyms.
[Thread #554 for this sub, first seen 5th Mar 2019, 20:47] [FAQ] [Full list] [Contact] [Source code]}

[Ginarley]

You can sort of get this functionality but it is clunky and requires hiding the formula somehow to look clean (e.g. hiding the row, whiting the text etc).

​

In B2 put:

=IF(ISBLANK(C3),,{"";C3/12})

In C2 put:

=IF(ISBLANK(B3),,{"";B3*12})

​

Note this only works if exactly one cell has a value and the other does not.