Can I have 2 cells whose formulas reference each other?

Welcome to the forum.

That is a "Circular Reference". In theory, you can create circular references but you have to set iterations in the options and it's an application wide parameter. In the grand scheme of things, not good.

You could do it with a VBA Worksheet Change Event handler monitoring the two cells.


Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.

Originally Posted by paulkinzelman

Thank you. Both ideas sound kind of ugly and hairy.

So the short answer for me is 'no'. :-)

Pretty much. The VBA option is probably simpler than it sounds, but if you're not familiar with VBA, maybe don't go there.

The simplest option would be to have two (optional) input cells; one for lbs and one for kg. Then output the converted value in adjacent cells using the formula. The output cells could be locked (and the sheet protected). You could maybe have a fifth cell that contains the conversion factor.

See the attached example.

I agree that this is an overly complicated way to do this (circular logic with two different input/output cells and each cell has to figure out at run time whether it is input or output). If it helps, here's how I typically set this sort of thing up:

1) Designate three input cells. One cell will be for the input mass. The other will be for inputting the units associated with this value. The third will be for entering the desired output units. For now, B1 is the mass, B2 is the input unit, B3 is the output unit.
2) In my output cell (maybe B4), enter a CONVERT() function =CONVERT(B1,B2,B3).

Now, as long as I input the correct unit text strings (see CONVERT() help file: https://support.microsoft.com/en-us/...d-666c810f9af2 ), then I can convert any mass between any two mass units by simply entering the desired values in B1:B3. If someone other than myself needs to use this spreadsheet, I might include a data validation list and rule for B2 and B3 to aid in entering the correct mass unit text (especially for something like lbm for pounds, since most people don't distinguish between lb-mass and lb-force).

I don't know if that helps, but that's how I like to do this sort of thing.

Here's an updated sample file with MrShorty's solution implemented.