If/Or statement not working when incorporating result of a drop down list

Jennifer_Reynolds

New Member
Joined
Jul 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi everyone, I have the below formula which is designed to return a blank in a Worksheet2 cell if there is no value in Worksheet 1 cell A11 or if Worksheet 1 cell E11 value is 'Targeted'. If neither of these conditions are met, the formula should return the value in Worksheet1 cell A11

=IF(OR('Worksheet1 '!A11="", 'Worksheet1 '!E11="Targeted"), " ", 'Worksheet1 '!A11)

Problem is that column E is a drop down list and it doesn't seem to work - the above formula does work if I for example use text from another column as part of the or statement but then doesn't seem to work for column E with the drop downs. Is there an issue with incorporating drop down lists that requires an alternative approach/solution?

Thank you!
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
65,467
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

No, it shouldn't be an issue if the value comes from a drop-down or from hard-coded text.
Usually, the culprit is an extra space somewhere in the value of your drop-down list.

When E11 is selected to be "Targeted", try entering this formula in any blank cell on "Worksheet1 ":
=LEN(E11)

If it returns anything more than 8, then you have some extra spaces in there.
 
Solution

Jennifer_Reynolds

New Member
Joined
Jul 8, 2022
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

No, it shouldn't be an issue if the value comes from a drop-down or from hard-coded text.
Usually, the culprit is an extra space somewhere in the value of your drop-down list.

When E11 is selected to be "Targeted", try entering this formula in any blank cell on "Worksheet1 ":
=LEN(E11)

If it returns anything more than 8, then you have some extra spaces in there.

Thanks, easy when you know how. Yes that was the issue, 9 characters rather than 8. Problem solved, thank you.
 

Forum statistics

Threads
1,175,833
Messages
5,899,728
Members
434,797
Latest member
natejxoticpc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top