Richard Sutherland
asked on
Excel multiple conditions (text)
I have two sheets in one excel workbook.
Sheet 1 has overkill information on customer sales that is obtained as a report. It includes a column for the customer's county in two letter format.
Sheet 2 has an abridged set of Sheet 1's information. Information incell A1 on sheet 2 is linked to Sheet 1. Example: Sheet1!A1
So far so good. Now I want to create a condition where the contents of a cell is displayed or not based on whether it meets a condition. If contents of column C on Sheet1 is either UK or FR then display exactly that data from Sheet1 ... if not then just leave the cell blank.
In my case above I want to highlight only those sales which come from European Union countries. (so there will be 28 different conditions in total not just two as shown above).
Assuming the country code is in Column A on Sheet1 and that information would go into Column A on Sheet2 I successfully created this formula to check a single condition:
=IF(Sheet1!A1<>"UK","",She et1!A1) If A1 shows UK on sheet1 then UK will be displayed on sheet2
What about if I want to say: if A1 shows either UK or FR on sheet 1 then display what is shows in that cell on Sheet 2? If not, leave the cell blank. Based on information I read I thought this would work but it doesn't:
=IF(OR(Sheet1!A1<>"UK", Sheet1!AH2<>"FR"), "", "Sheet1!A1)
Quite long-winded perhaps so please forgive.
Sheet 1 has overkill information on customer sales that is obtained as a report. It includes a column for the customer's county in two letter format.
Sheet 2 has an abridged set of Sheet 1's information. Information incell A1 on sheet 2 is linked to Sheet 1. Example: Sheet1!A1
So far so good. Now I want to create a condition where the contents of a cell is displayed or not based on whether it meets a condition. If contents of column C on Sheet1 is either UK or FR then display exactly that data from Sheet1 ... if not then just leave the cell blank.
In my case above I want to highlight only those sales which come from European Union countries. (so there will be 28 different conditions in total not just two as shown above).
Assuming the country code is in Column A on Sheet1 and that information would go into Column A on Sheet2 I successfully created this formula to check a single condition:
=IF(Sheet1!A1<>"UK","",She
What about if I want to say: if A1 shows either UK or FR on sheet 1 then display what is shows in that cell on Sheet 2? If not, leave the cell blank. Based on information I read I thought this would work but it doesn't:
=IF(OR(Sheet1!A1<>"UK", Sheet1!AH2<>"FR"), "", "Sheet1!A1)
Quite long-winded perhaps so please forgive.
=IF(A1="UK", Sheet1!B2, IF(A1 = "FR", Sheet1!B3, ""))
However, you will not be able to differentiate which FR city to use.
However, you will not be able to differentiate which FR city to use.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you to all contributors, Subodh and Brian. Rogonzo's solution worked best for me once I eliminated a quote which was extra.
Appreciate your help a lot.
Richard
Appreciate your help a lot.
Richard
On Sheet2
In A1
Open in new window
and then copy across and down.Please refer to the attached.
Richard.xlsx