If or Match formula quick use

[raindrop]

Hello, I am trying to do the following:

If there is an X in any one of the cells: A1 to A5, than I would like to input a Yes in cell A6. If any one of the cells does not have an X in it, than I'd like to leave it blank.

I have come close with a few formulas, but not quite there.. any tips?

=IF(A4="X","YES",0) * NOT quite for the whole range, but close
= MATCH("X",A1:A5,0) * Almost
=VLOOKUP(b8,A1:A5,"YES","") * No luck.
<table border="0" cellpadding="0" cellspacing="0" width="74"><col width="74"><tr height="19"> <td class="xl66" style="height:14.4pt;width:56pt" height="19" width="74">
</td> </tr></table>Thanks

 

[T. Valko]

Hello, I am trying to do the following:

If there is an X in any one of the cells: A1 to A5, than I would like to input a Yes in cell A6. If any one of the cells does not have an X in it, than I'd like to leave it blank.

I have come close with a few formulas, but not quite there.. any tips?

=IF(A4="X","YES",0) * NOT quite for the whole range, but close
= MATCH("X",A1:A5,0) * Almost
=VLOOKUP(b8,A1:A5,"YES","") * No luck.
<TABLE cellSpacing=0 cellPadding=0 width=74 border=0><COLGROUP><COL width=74><TBODY><TR height=19><TD class=xl66 style="WIDTH: 56pt; HEIGHT: 14.4pt" width=74 height=19>

</TD></TR></TBODY></TABLE>Thanks

Try this formula entered in A6:

=IF(COUNTIF(A1:A5,"X"),"Yes","")

 

[raindrop]

That is perfect!!! Thanks. That is awesome.

Last question:
1) Is there any way to bold/ color the yes within the scope of the formula?
2) Is there a formula to color, BOLD, Bigger Font the following:
In column A: If Y, than I'd like to BOLD, BIGGER FONT, and place a different color say blue. If N, than I'd like to make smaller font and color in grey. Is there a formula for this one???

Thanks!

 

[T. Valko]

That is perfect!!! Thanks. That is awesome.

Last question:
1) Is there any way to bold/ color the yes within the scope of the formula?
2) Is there a formula to color, BOLD, Bigger Font the following:
In column A: If Y, than I'd like to BOLD, BIGGER FONT, and place a different color say blue. If N, than I'd like to make smaller font and color in grey. Is there a formula for this one???

Thanks!

You can use conditional formatting to do MOST of that. You can't use conditional formatting to change a font size, however (at least in Excel versions thru Excel 2007. I don't know about Excel 2010).

What version of Excel are you using?

 

[raindrop]

Thanks... I can't believe I didn't think of that... one day I'll get there... .I hope. I was able to make the changes, with of course the exception of the font size. Looks like it was forgotten to include in the conditional formatting... bummer. If you know of any work arounds, please advise. I work in Excel 2007.

THANKS!!!!!

 

[T. Valko]

Thanks... I can't believe I didn't think of that... one day I'll get there... .I hope. I was able to make the changes, with of course the exception of the font size. Looks like it was forgotten to include in the conditional formatting... bummer. If you know of any work arounds, please advise. I work in Excel 2007.

THANKS!!!!!

You can probably do the font size change with an event macro which requires VBA programming.

If you still want to pursue that then I suggest starting a new thread with a subject line of something like: "Automatically change font size when condition is met".

I'm not much of a programmer so someone else will need to help out with that.

Good luck!