Excel macro to toggle between currencies the only way?

[ladyaimee]

Hi guys - would love ideas to fit my requirement

I would like to be able to toggle back and forth between 2 currencies - AUD & USD - with a conversion rate that our finance dep uses (example 0.72 right now)
My spreadsheet is full of Project costs for years, current actuals, monthly forecasts, summations, etc. All the options I've investigated to make this happen aren't possible as you would have to apply the functions like VLOOKUP or INDEX/MATCH to each cell or create new columns. Same with the IF function and drop-down list.

There's only 2 currencies but it would be too much having 2 columns (AUD & USD) for every month/column as it's already cluttered enough. I would just like to not change the format of the information displayed and toggle to be able to change all numbers on the sheet between the two currencies within the same cell, not to double the amount of columns. Also at the end of each month, projects update me with their new forecast figures so I just copy and paste these new figures over the current ones - which is why applying a lookup formula within each cell would be to cumbersome.

Would love to hear any ideas or know if what I'm trying to do is too ambitious without dipping into VBA - because when it comes to macros I'm just not advanced enough to understand! Although if one is already written for this exact thing and you know about it I'd love to hear from you!!

Happy to send my current spreadsheet to anyone willing to have a look Thanks in advance!

 

[mole999]

How about as a theory

Cell A1 input A for Aus and test that only

then in your value cell

=IF($A$1="A", "Multiply by AUS value", "Multiply by USD value")
so if its not A (AUS) then it can only ever be USD

B1 and C1 might contain the conversation values you need, so you only ever need to update there, it could also be a separate sheet

 

[Dryver14]

you could try with each cell for conversion (which I assume is getting there from some sort of formula) referring again to another cell for instance A1

so which ever way round it is in each amount formula at the end put *A1 then in A1 you either type 1 or .72

 

[ladyaimee]

How about as a theory

Cell A1 input A for Aus and test that only

then in your value cell

=IF($A$1="A", "Multiply by AUS value", "Multiply by USD value")
so if its not A (AUS) then it can only ever be USD

B1 and C1 might contain the conversation values you need, so you only ever need to update there, it could also be a separate sheet

Thanks for your suggestioN! would I need to put this IF statement in every cell that currently contains a value? My spreadsheet already contains hundreds of values, and I copy and paste over these values with updated ones on a monthly basis, so I would need a solution where I didn't have to add an IF statement inside a cell that already contains a value..

 

[ladyaimee]

you could try with each cell for conversion (which I assume is getting there from some sort of formula) referring again to another cell for instance A1

so which ever way round it is in each amount formula at the end put *A1 then in A1 you either type 1 or .72

Thanks! I have thought of this one however I would like a solution where I do not have to change every existing cell

 

[Dryver14]

short of inserting a column alongside and then dragging down the above formula and then deleting the original I think you may be stuck with vba