Search for "H" in row, return 'Yes' or 'No'

[Jelmer]

Hi,

I made a personnel tracker with on the left side column the names, on the first row the dates and then I fill in for everyday someone is working a 'W' and when they have a holiday I fill in an 'H'. The thing I want to do is to show only the rows that have one ore more 'H' behind the name and leave out the ones that don't have a planned holiday.

I tried adding an extra column with for each row a formula that returns 'Yes' when there is a row with an 'H' in it and 'No' if it doesn't so I can then add a filter to that column and show only the people with planned holidays. Sounds pretty easy but I can't figure out what formula I should use. I played with the LOOKUP function but so far no success...



Can anyone help me out here?

[Pecoflyer]

Hi and welcome
try something like =IF(COUNTIF(B$2:X$2,"H"),"Yes","No")
Adapt range as needed and pull down

[Jelmer]

Originally Posted by Pecoflyer

Hi and welcome
try something like =IF(COUNTIF(B$2:X$2,"H"),"Yes","No")
Adapt range as needed and pull down

Thank you for your quick reply. I tried this but it returns 'No' for rows without an "H" but not a 'Yes' for rows that do contain an "H".

Some more info: I had to replace the , with ; and I'm currently working in excel 2010.

[Pecoflyer]

Perhaps post a sample sheet (no pics please)

PS if you do not indicate your location, it is difficult to use the correct regional settings

[Jelmer]

Could you tell me how to do that?

[Pecoflyer]

My mistake try
=IF(COUNTIF($IA10:$xfd10;"H");"Yes";"No")
and pull down

[Jelmer]

Thank you! That solved the problem!