Creating a Text value from a range of cells dependant on the answers.

[Tazyote]

I tried searching the forum and I think a big part of my problem is trying to figure out how to express what I am trying to do. I am certain the answer is easy and has already out there but I am going to throw it out there anyway.

I have a line of cells with Drop down Lists. The Cells are in the range of J2:P2. The Drop down has 3 options, Yes, No, and NA. What I am trying to do is make Cell R2 Read either "Pass" or "Fail" if any cell in the range equals "No". I have tried assigning numberical values in hidden cells (I can only assign numbers for two of the values), I have tried Assign the values to new cells and use If then formating: =IF(J2:P2=A24,"Fail", IF(J2:P2=A23,"Pass", IF(J2:P2=A25,"PassIf("J2+"","")))) and I just can't figure out how to get the cell to work. Any ideas on how to do this?

This is what I get with the formulas so far:
J2 K2 L2 M2 N2 O2 P2 R2
Yes Yes Yes Yes Yes Yes Yes #VALUE!


I want it to do this:
J2 K2 L2 M2 N2 O2 P2 R2
Yes Yes Yes Yes Yes Yes Yes Pass
or:
J2 K2 L2 M2 N2 O2 P2 R2
Yes No Yes Yes Yes Yes Yes Fail

[gsnidow]

Try this in cell R2...

Please Login or Register  to view this content.

There is probably a way to scan the entire range, rather than hard coding all of them, but this will work.

Greg

[Tazyote]

I figured out another solution as well. I assigned each answer in each cell in the range a numerical value by formating a second hidden cell as:

=IF(J2="No", 0, IF(J2="Yes", 1, IF(J2="NA", 1, IF(J2="", 0)))) .

I then summed them in another cell and then formatted the final cell as,

=IF(BJ2<7,"Fail",IF(BJ2=7,"Pass"))

this seems to work.

[Tazyote]

I actually switched over to the code suggested by gsnidow and it works great. Way simpler then my number code. However, I was wondering if there was also a way to make the final cell read "" (blank) if any of the cells in the range were also left blank? Ideas?

[gsnidow]

Try this. It's getting kind of big, but I still think there is a better way. I'll do some googling and post back if I come up with something better.

Please Login or Register  to view this content.

Greg

[Tony Valko]

Maybe this...

=IF(COUNTIF(J2:P2,""),"",IF(COUNTIF(J2:P2,"No"),"Fail","Pass"))

[Tazyote]

Wow! It works! Thanks so much. I need to learn more about the Countif function.

[Tony Valko]

You're welcome. Thanks for the feedback!