I'm trying to create a complex formula. I tried to upload a screenshot of it but for some reason, the screenshot won't upload. So here's the problem. Picture 2 cells side-by-side such as A1 AND B1. A1 is a number in kilograms (metric system). B1 is a number in pounds (imperial system). When you type a number into A1 it automatically gets converted into pounds for B1. However, B1 cannot do the same thing for A1 because that causes the Circular Reference Problem. Here's where it gets very complicated because I need both cells to work interchangeably without causing a Circular Reference Issue which would stop it from working.
I need a formula that will basically do the following 3 things.
- The formula entered into A1 must tell A1 this information - If B1 is empty right now than cancel out (or just ignore) the formula in B1. Do not let B1 transfer anything or convert anything into A1. Only the formula in A1 is allowed to send its converted data to B1. Just not vice versa.
- The formula entered into B1 must tell B1 this information - If A1 is empty right now than cancel out (or just ignore) the formula in A1. Do not let A1 transfer anything or convert anything into B1. Only the formula in B1 is allowed to send its converted data to A1. Just not vice versa.
- If possible, both of the formulas also need to have an IF, OR, IFBLANK function included in it as well so the formula will still be active in the cells even if all of the cells are blank.
In other words, the formula allows A1 to cancel out the formula in B1 so that a Circular Reference Issue won't occur. But A1 still performs it's function and converts whatever number was entered (in Kilograms) to B1 (in pounds). So if type 10 into A1 then it would appear as 22 in B1. Because 10 Kilograms is 22 Pounds (10 x 2.2). But since the formula in B1 already got canceled out by the formula in A1, B1 will not try to convert the 22 back into 10 and send it back to A1 (10 / 2.2). AND VICE VERSA IF YOU ENTER A NUMBER INTO B1 FIRST, INSTEAD OF ENTERING A NUMBER INTO A1 FIRST. A1 and B1 work interchangeably.
Yes, this is a really tricky formula. Any help will be hugely appreciated.
Bookmarks