IF statement when selcting from a data validation list

Microsoft Excel 2010 - complete product...
May 18, 2017 at 01:17:10
Specs: Windows 7
Hello,
in my workbook I am having a little issue.

I have a data validation list of currencies, cell E9, GBP, EUR, SEK etc etc
I have a user inputted field called FX RATE, cell G5

I now require the cell H5 to either show 1.0000 if GBP is the selected currency in E9 or FX RATE from G5 if any other currency is selected.
I figured to put in the H5 cell.... =IF(E9 ="GBP", 1.0000, G5) though this doesn't work.
I presume its because excel doesn't like the validation list.

Any help would be great:)

message edited by dishythefish


See More: IF statement when selcting from a data validation list

Reply ↓  Report •


#1
May 18, 2017 at 01:40:23
i have just tried this and it works for me

So

E9 has my drop down list gbp, eur, sek

G5 has the formula '=E9'

H1 has the formula '=IF(E9="gbp",1,G5)'

If I select gbp from the list in E9, H5 changes to 1
if I select anything else from E9 then H5 changes to that

The only thing I can think of is that, in your list you are using lower case and in your formula you are using upper case

=IF(E9 ="GBP", 1.0000, G5)

This could be the reason for your version not working.


Reply ↓  Report •

#2
May 18, 2017 at 01:46:40
Sorry I wasn't specific, my drop down list are all in Caps, so its not a case issue.
My list options are stored on sheet 2 in the workbook, could this be part of the issue?

message edited by dishythefish


Reply ↓  Report •

#3
May 18, 2017 at 03:16:58
If you PM me I can send you an email address you send me your workbook to? unless someone else is able to jump in and help.

Reply ↓  Report •

Related Solutions

#4
May 18, 2017 at 03:23:06
Thanks AlwaysWilling, my colleague just gave me this and it worked:)

=IF(RIGHT(E9,3)="GBP","1.0000",IF(RIGHT(E9,3)="ncy","",$G$5))


Reply ↓  Report •

#5
May 18, 2017 at 03:46:04
Strange, I didn't have to do this on my workbook, but im glad you got it working. Thanks for coming back to let us know.

Reply ↓  Report •

Ask Question