Question description:
This user has given permission to use the problem statement for this
blog.
I want to display a range of cells if they meet a specific criteria. I want to display a name IF it does not equal one of three options AND there are any numbers contained in a range of cells
Solved by G. F. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/04/2018 - 07:57
Hello, Welcome to Got It Pro
Excelchat Expert
04/04/2018 - 07:58
Are you there?
User
04/04/2018 - 07:58
yes
Excelchat Expert
04/04/2018 - 07:59
How may I help you?
User
04/04/2018 - 08:00
I want to display all information on a customers order, based on several conditions but I am unsure how to do so
Excelchat Expert
04/04/2018 - 08:00
Can you please tell me the conditions?
Excelchat Expert
04/04/2018 - 08:01
and what information you want to display?
Excelchat Expert
04/04/2018 - 08:03
Hello?
User
04/04/2018 - 08:03
IF Column A does not contain customer A.B,C
Excelchat Expert
04/04/2018 - 08:04
Yes?
User
04/04/2018 - 08:04
AND there is a number in any cells between B-G
User
04/04/2018 - 08:05
then display all of the information in row 2,3,4 etc
Excelchat Expert
04/04/2018 - 08:06
you mean to say that if column A is blank and there is any number from B2 to G7 then it should display the row 2,3,4 data?
Excelchat Expert
04/04/2018 - 08:06
Right?
User
04/04/2018 - 08:08
no so to take customer A as an example, if A2 does not contain either A,B or C AND there is a number in any of the cells B2-G2 then display the information in cells A2-G2
User
04/04/2018 - 08:08
so in the second sheet, row 2 would display as False as it would not meet all conditions, as A2 does contain A
Excelchat Expert
04/04/2018 - 08:09
What's A? A2 has A in it actually.
User
04/04/2018 - 08:09
however, row 5 would display information as it meets both conditions, there is not an A,B or C in A5 AND there is a number in one of the specified colums
Excelchat Expert
04/04/2018 - 08:10
you wrote that " if A2 does not contain either A,B or C " .. But how A2 is supposed to have A,B,C.?
User
04/04/2018 - 08:10
yes because the same formula will be applied to each row
User
04/04/2018 - 08:11
so if there is an A then it will simply display false
User
04/04/2018 - 08:11
but row 5,6,7 would not display false as they do not contain the letters, A,B or C
Excelchat Expert
04/04/2018 - 08:12
Row 7 is customer F and it has value in C thats is 7
Excelchat Expert
04/04/2018 - 08:12
I am not getting you
Excelchat Expert
04/04/2018 - 08:13
It looks like you are confusing the column Alphabets and customer names alphabets
User
04/04/2018 - 08:13
no i am not
Excelchat Expert
04/04/2018 - 08:13
Can you please change this data so it would be more clear and easy to explain?
User
04/04/2018 - 08:14
easier now
Excelchat Expert
04/04/2018 - 08:14
yes now tell me
User
04/04/2018 - 08:16
if any cell in column A does not equal Chris, Bob or Sarah AND there are any numbers in B-G, then display the information in the corresponding row
User
04/04/2018 - 08:20
I have added a formula to see if that is any more explanatory, of course this does not actually work
Excelchat Expert
04/04/2018 - 08:21
I have to ask you one question.
Excelchat Expert
04/04/2018 - 08:22
If column A does not equal chris, bob and sarah then whose information you want to display?
User
04/04/2018 - 08:22
just a false response
Excelchat Expert
04/04/2018 - 08:23
They all will be false then
User
04/04/2018 - 08:24
no they wont, row 5,6 and 7 do not contain Christopher, Bob, or Sarah
Excelchat Expert
04/04/2018 - 08:24
Okay. Let me check
Excelchat Expert
04/04/2018 - 08:28
Check column J
Excelchat Expert
04/04/2018 - 08:28
sorry Column H
Excelchat Expert
04/04/2018 - 08:29
Actually number condition will give all False because number is coming in all the columns from B2 TO G7
User
04/04/2018 - 08:30
yes the formula is correct for the name of customer, but I need it to also test the condition of the numbers
Excelchat Expert
04/04/2018 - 08:31
If you will add the number condition, it will all show FALSE
User
04/04/2018 - 08:31
why?
Excelchat Expert
04/04/2018 - 08:32
Because number is coming in all the columns
User
04/04/2018 - 08:33
yes but in row 1-3 it does suit the primary condition
User
04/04/2018 - 08:34
does not*
Excelchat Expert
04/04/2018 - 08:36
It does, there are numbers in Column B,C,G in row 2,3,4
User
04/04/2018 - 08:37
but the primary condition is that it is not Chris, Bob or Sarah and it is
Excelchat Expert
04/04/2018 - 08:37
You want to put an OR between the numbers and name condition?
Excelchat Expert
04/04/2018 - 08:38
because u said that its an AND condition and in AND if both are met then it displays the result
User
04/04/2018 - 08:38
no an AND
User
04/04/2018 - 08:38
yes thats what I want
User
04/04/2018 - 08:39
but if the conditions are met I want it to display the information in the corresponding sell, not TRUE
User
04/04/2018 - 08:39
the formula in column I doesn't function correctly
User
04/04/2018 - 08:40
based on the formula row 5,6 and 7 should display as TRUE
Excelchat Expert
04/04/2018 - 08:41
Okay let me see.
Excelchat Expert
04/04/2018 - 08:42
There are numbers in column B,C,D,E,F,G
User
04/04/2018 - 08:45
yes
Excelchat Expert
04/04/2018 - 08:47
Actually the problem is that ISNUMBER function works for one value only
Excelchat Expert
04/04/2018 - 08:48
we are putting a range so its not giving the correct result
User
04/04/2018 - 08:50
ok
Excelchat Expert
04/04/2018 - 08:54
Please check
Excelchat Expert
04/04/2018 - 08:56
The session time is about to end. Please do leave your kind rating after session ends.
Excelchat Expert
04/04/2018 - 08:56
Thank you for using Got It Pro. Have a nice day :)
Excelchat Expert
04/04/2018 - 08:57
=IF(AND(A$2:A$7<>"Chris",A$2:A$7<>"Bob",A$2:A$7<>"Sarah",ISNUMBER(B$2),ISNUMBER(C$4),ISNUMBER(G$3),ISNUMBER(D$5),ISNUMBER(E$6),ISNUMBER(C$7)),TRUE,FALSE)
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
