Change currency when selected in dropdownlist (conditional formatting) and more

[The excel apprentice]

First of all, i work in a dutch version of excel, but i have uploaded examples before and have never had complaints that this doesn't work for people whom use the english version (maybe it translates the functions, i simply do not know). I would upload this in a dutch part of this forum, but there doesnt seem to be one for Excel 2007.

I have made conditional formatting that changes the currency symbol when i select it in a dropdownlist (cost sheet).
If i change from EUR to USD this works (also the other way around), but only the first time. So i cant select USD then decide it should be EUR afterall, because then suddenly it doesn´t seem to work. If i ''reset the 'condit format' this will work again, but only for one time'.

I have also got 2 price table´s (cost sheet) one for EUR and one for USD (usd draws data from the EUR table and multiplies it with the exchange rate).

I have tried to create a IF function that should (in the quotation sheet)
IF (EUR = true : draw it from the eur table, and if not true draw it from the USD table. But for some reason this does not seem to work.

If anyone could help me out with either of these problems, that would be greatly appreciated.

[AliGW]

In F15 copied down:

=IF(costs!$D$4="EUR",costs!C10,costs!D10)

Conditional formatting rules:

=costs!$D$4="EUR"

=costs!$D$4="USD"

PS You were making it far too complicated! Everything is determined by that one cell - costs!$D$4.

[The excel apprentice]

I already was kind of aware that i was overcomplicating things, but I wouldn't know how else it would be possible (for the condit).

For the condit format:
i get an error when i use your formula (actually the reason why i went all complicated on this):

- You cant refer other worksheets/workmaps for conditional formatting (such a bummer)


The ''IF'' formula works like a charm in my example sheet, so i am going to implement that in my actual sheet with great joy, thanks a lot!

[AliGW]

- You cant refer other worksheets/workmaps for conditional formatting (such a bummer)

Where did you get this idea? Maybe it's a limitation in Excel 2007, but it works in 2010 and above - I have tens of workbooks that depend on it - I've been using it for years! I had it working in your spreadsheet, too, but I didn't keep the workbook - sorry!

[The excel apprentice]

Well unfortunately i am doomed to work with excel 2007. Is there anyway i can still get this to work? I think my over complicated thing should work (it kinda does), but only for once and i can't comprehend why that is.

If you have any suggestions, i would greatly appreciate that, otherwise thanks a lot since you already helped me out quite a bit!

[AliGW]

A quick Googling of this reveals that you can do it in Excel 2007 by naming the range, so make costs!D4 a named range and then use the named range in the CF formula.

[The excel apprentice]

I'll go ahead and google in order find out what that means, thanks again!

[AliGW]

http://answers.microsoft.com/en-us/o...75cd63c?auth=1

[AliGW]

You need to assign a name to the cell costs!D4. To do this, you need to find the Name Manager - in 2010 and above it's on the Formulas ribbon.

[AliGW]

See if the attached works in Excel 2007.

[The excel apprentice]

I just started working on it again (just had lunch). I am now at the point that i have assigned a name to the cell costs!D4 (name: currency), and changed the CF formula to
=currency="EUR"
=currency="USD"

However this doesn't seem to work since it now doesn't give any formatting anymore, but i haven't really tried fixing it, since i just got at it again.

Also i think you intended to attach a workbook, but it doesn't seem like you did.

[AliGW]

I did and it is now attached to two of my posts!!! It should work without a problem - try it.

[The excel apprentice]

Unfortunately for me it doesn't seem to work.
What i find strange is that the cells their selves don't have any formatting (i believe they call it default, in the english version), but the document shows '€' signs, whether i insert EUR/USD or empty cell D5

[The excel apprentice]

Update:
If i insert a value (i tried 1) in cell G16, G17 or G18 and then continue to remove that value again it will change to dollar (if selected in D4). When i continue to change D4 back to euro's it changes back without any issues.

[AliGW]

Why are you entering values into those cells? Which sheet???

Why are you now using D5 for the currency? It is D4 in your workbook (and the version I have attached).

Are you telling us everything that you are doing?

[The excel apprentice]

Well in my first version (with the complicated stuff), i accidentally inserted a value there and it seemed to ''activate the CF'', because suddenly it did change currency. In a moment of disparity i tried it again (in your example) and it works, but naturally i dont want to have to insert and delete random value's in those cells in order for my CF to work.

It was in the mentioned cells in the the quotation sheet.

[AliGW]

There are no values in those cells in column G on the quotation sheet. The CF formula I have given you does not depend on those cells anyway.

[The excel apprentice]

No, i understand that. But for some ''magical reason'' that i don't understand it does actually effect the CF when i insert values in those cells, but only after i remove them again.

So lets say if D4 (sorry for previously naming it D5, that was a typo) = EUR, and i change it to USD the values change perfectly(thanks again), but the currency sign still doesn't change. Early on (with the complicated formula) i found that if I that i go to one of those G-cells i mentioned before and i insert a value (i did insert 1), still nothing happens (as it should, because that cell has no CF, no formula's, nothing whatsoever). HOWEVER if i remove that value again it seems to trigger the CF in the cells in the kolums of the left of it, and then they actually start doing what they should have been doing in the first place. I only tried it again in your example because nothing seemed to work, but strangely enough it did show euro signs (even if D4 was USD or empty). I found that rather strange, because the CF formulas where identical (except "EUR" / "USD"was different) AND the formatting of the cells (not the conditional) was not set on currency.

So i simply wondered if putting values in those G-cells, would still effect (since the formula has stranged) the CF, which it does (but this means nothing to me, but i inform you because i thought it might indicate what the problem is to someone (like you) who actually knows what he/she is doing.

[The excel apprentice]

the formula has stranged = the formula has changed , my bad

[AliGW]

the formula has stranged = the formula has changed , my bad

Why? I am getting very confused! Which formula has changed? Why have you changed it? The spreadsheet I gave you works: you are doing something to stop it from working and we need to work out what, so please be very specific and clear about what you have changed.

[The excel apprentice]

because first they were something like
=C23=""all prices are in USD and exclusive taxes (VAT). The cleint remains responsible for any tax payments an"
=C23=""all prices are in EUR and exclusive taxes (VAT). The cleint remains responsible for any tax payments an"

And now
=currency="EUR"
=currency="USD"

[AliGW]

Yes, and?

'Currency' is the name referring to range costs!D4.

[The excel apprentice]

yes i understand, but thats what i meant when i said the formula (as in the formula which determines when to use which currency in the CF) has changed.

[AliGW]

So why is it not working?

So lets say if D4 (sorry for previously naming it D5, that was a typo) = EUR, and i change it to USD the values change perfectly(thanks again), but the currency sign still doesn't change.

Yes, it does. Please attach the new (changed) workbook and I'll sort it out for you.

[The excel apprentice]

I think it might be working for you, since I dind't change it. I think i confused you due to my bad english and/or excel knowledge, so i'll do my best to explain.

- I did not change anything in the workbook you attached previously (the one called currency changer).
- Said workbook did not change the currency --> the symbols (as i mentioned in the quote).
- It did change values (the numbers, not the symbols)
- I believe you when you say it does change for you (prob. because you work in 2010), but in mine it simply does not.

[AliGW]

I see. It should work - I know of no limitation in Excel 2007 that should stop it from doing so, but I don't have that version here. I am working in Excel 2016. Maybe someone with 2007 could also check the file I provided to see if it really is 2007 or something peculiar about your system.

[The excel apprentice]

I think that would be best, yes.
I do wan't to thank you once more for putting up for me this long! I really appreciate your time and effort!

[AliGW]

Not a problem!!!

[JohnTopley]

Jumping in here

in F15 in "Quotation"

=INDEX(costs!$C$10:$D$13,MATCH(Quotation!$C15,costs!$A$10:$A$13,0),MATCH(costs!$D$4,costs!$C$9:$D$9,0))

will return correct value and currency symbol from table in Costs

in F19

=IF(costs!D4="EUR",TEXT(SUM(F15:F18),"€ #,##0.00"),TEXT(SUM(F15:F18),"$ #, ##0.00"))

will format TOTAL with correct currency symbol

[AliGW]

A much better idea, John!

[JohnTopley]

P.S Change headings on Cost table to "EUR" and "USD" only

[The excel apprentice]

Thanks a lot John, i haven't tried it out yet (and i don't think i will have the time for that anymore today), but i sure will try tomorrow!

[JohnTopley]

Sample attached.

[The excel apprentice]

Goodmorning John,

I have checked your example to see if it works.
The sum formula seems to work on some level (the symbols change, awesome!).

However the 2 valuta's end up being noted as:
- $ 011 which should be $11,40
- € 10,000 which should be €10,00

For the individual prices i see you have changed the formula, but the outcome remains the same.
- The value's change perfectly
- The symbols only change when i insert and delete an random number in a cel next to it (as i described as best as a can above in this thread)

It might be due to that i should change the headings in the cost table as you mentioned, but i am not sure what you mean with that. Do you mean i should kollum C to EUR and Kollum D to USD? (i am not sure if this is possible, so i am gonna google that naturaly). If that's not what you mean, i don´t think i understand what to do.

I assume this example worked for you in excel 2010, so maybe i'll just have to wait for someone who is doomed to work in 2007 like me. I want to thank you for the effort again, because i am really appreciating this site and it's community, since i could barely do this task i currently have without it.

[JohnTopley]

They don't in the sample I sent: the answers are correct. Do you need to change "," to ";" in the formulae?

It should work in 2007. I saved the sample as 97-2003.xls file and it worked OK.

Just save the file I sent as 2007 file.

[JohnTopley]

P.S.

Add ERROR handing round formula ..

=IFERROR(INDEX(costs!$C$10:$D$13,MATCH(Quotation!$C15,costs!$A$10:$A$13,0),MATCH(costs!$D$4,costs!$C$9:$D$9,0)),"")

The symbols only change when i insert and delete an random number in a cell next to it

Do not understand what you are doing re the above. ALL the symbols change automatically

[AliGW]

Do not understand what you are doing re the above. ALL the symbols change automatically

John - this is where I got lost yesterday! I never got to the bottom of the anomaly that the OP is describing.

[JohnTopley]

@Ali ... It's very frustrating (and confusing) when this happens given I supplied a working example

[The excel apprentice]

I am really starting to wonder if i am plain stupid, or my excel/system is simply messed up. Anyway:
- I saved it as an 2007 and also tried 97-2003
- i tried adding the IFERROR, but then suddenly it says the formula isn't working (I asume i am doing something wrong, but i also tried copying your formula and changed IFERROR to ALS.FOUT (dutch command version).
- When i download your file ","is already changed to ";"

However it still doesn't seem to work for me *fruuuustraation*


And for the anomaly, i am clueless as well, but i thought it might be helpful to describe. First i thought it might be, because otherwise the formula's were not activated or smt, but i checked and they are set on automatic. This has me confused just as much as you guys (maybe even more, since this is all kinda new to me).

Also i just noticed that if i use the anomaly to set it to USD signs, it works the other way around:
- So if they are in USD and i select EUR in costs!D4 it changes back to euros perfectly (except that the sum has one decimal to much)


An easy way out for me would be to simply not use currency symbols but let the cell above or next to it display USD/EUR, but i really want this to work after all this time we have been struggling with this.

[AliGW]

Would it be possible to provide either screenshots or a little video of what is happening for you? They say a picture paints a thousand words!

PS Yes, we really want it to work, too!!!

[The excel apprentice]

I will try and make a picture compilation thingy (wouldn't know how to make a vid).

[The excel apprentice]

I have added an extra sheet with screenshots.
If there is any other action you require to get a better immage of problen let me know.

[AliGW]

I think I have an idea what is happening! This may be to do with your locale and currency separators: in the UK, we use "." for a decimal and "," as a thousands separator, but for you, it's the other way round. You are going to need to tweak the formatting applied via formulae and CF to match your locale settings.

[The excel apprentice]

I only now notice that my old complicated CF formula is still in the file, i wonder if John was aware of this and if this should stay?

I am gonna look if i can tweak it, although i don't have that much faith in my excel capabilities. If you have any more advice on how to approach the tweaking that could help, otherwise i am just gonna try some stuff and (if i dont succeed) ask my boss next monday if he can take a look at it since he is used this this excel/system and location.

[AliGW]

Remove it and see what happens, but the problem I think is the one I have identified, although if I am right, only you can verify it.

[The excel apprentice]

You are a genius! I got it to work MUCH better.
The only issue right now is that the sum of the price in euro's is displayed as 10,0,00, but i am gonna see if i can tweak it some more !

[AliGW]

That's because of the notation (decimal and thousands) issue I mentioned, I think: change it round in the formula and you should be fine. Genius? Nah! Stubbornly determined? Yes!!!

[The excel apprentice]

I managed to fix that issue.
Now ''all'' i have to do is to see if i can make this work in my actual sheet, which is gonna be a huge challenge.

[AliGW]

No, it's not: first, make notes in your example sheet of the things that have been done. Copy the various formulae and store them in different cells without the = sign so that you can easily copy and paste. Work one step at a time and check that each stage is working before moving on to the next. Do NOT change anything else whilst you are working on this - it might muddy the waters. And good luck!

[The excel apprentice]

Thanks for the advice, those are some pretty nifty tips.
Monday I will see if i can implement this in my actual sheet (which is a bit more complicated), but it should work after some fine tuning. For now I think it is wise to take a break from this, and focus on my other duties for the day.

I feel like i can't thank you guys enough, so once more THANK YOU!

And enjoy the weekend

[JohnTopley]

Ali,
Many thanks for your help in (hopefully) sorting out the issues as I was otherwise occupied this morning.

John

[The excel apprentice]

John, it works like a charm (in the example sheet). Many thanks for your effort and input!

[AliGW]

Ali,
Many thanks for your help in (hopefully) sorting out the issues as I was otherwise occupied this morning.

John

Not a problem! Seeing the screenshots was a light bulb moment. Hopefully it will all work out now.

[The excel apprentice]

After an hour or two i have gotten it to work in my real document, however i still have one problem.
I have described said problem with a few screenshots on blad4 (sheet4).

In the example sheets (that are still attached) i have the same problem. I was wondering if:
- there is a simple solution for this, since it has only issues going to dollars, no issues going back to euro's whatsoever.
- "€ #0,00" <-- this thing that dictates how is should be shown, can be put in the other formula's?

[The excel apprentice]

And this time i have actually attached the attachement, my bad!