Forum Discussion
Calculating Two Cells Based on each other
I have a basic task i am trying to complete and i am going through the web to learn how to solve this. Looking for help.
I have CAD Currency and USD Currency. I have two cells side by side and depending on what i put in one i want the other to adjust.
So if enter to dollars CAD in once cell the other cell will adjust to the equivalent in USD based on a exchange rate in another cell.
Any assistance on this would appreciated or even a link to resource to learn how
11 Replies
Sorry to come so late to the party and still, where did the idea that 'Two Cells Based on each other…' could be calculated come from?
Why would you not expect trying to calculate two - or any number of - cells 'based on each other…' to stop your project, reporting a circular reference?If you have two cells, one for CAD and another for USD, you can't use each of them both as source and as result, at least working with formulas only. You need source CAD, source USD (to enter data); result CAD and result USD (calculated by formulas) plus one cell for exchange rate.
SergeiBaklan Yes I have gotten that far. I can write the formulas to perform the calculations one way or the other but then you lose the formulas depending on where you enter the original number. I have been reading that I may need to use VBA to achieve this. This is outside of my knowledge and have been attempting to find solutions online that I can duplicate.
Maybe one simple way to do this, without VBA, is via a dropdown box that asks for either CAD or USD, after which you enter the amount, and there's an IF formula that changes the direction of the conversion from CAD to USD or vice versa.
I was just in Canada and recall the exchange rate as somewhere around 1.3 CAD to 1 USD, so that's the factor I put in, but it can be easily changed. Here's what the screen looks like.
