IF, AND, OR function with multiple cells

[williamjtmarsh]

Hi guys, new here and forums, so apologies for naivety and if I break any basic rules to forum'ing.

I am putting together a project management file and have multiple sheets that are auto populating between each other. All is working great, but the one thing I am struggling with is the below

I have lots of cells (processes) and once all the cells (processes) are completed, they are all filled in with "Y", a cell on another sheet is auto populated with a "Y" only when they are all filled in with a "Y". This is easily done, but I want to add into the mix a "NA" where a certain process may not be applicable to that project. The aim is when all cells are filled in with either a "Y" or a "NA" it will auto populate the cell on the other sheet with a "Y".

ALL cells have to be filled in and any combination of "Y" and "NA". Some combinations have 10 processes, some have 2

Would appreciate the help as I cannot find anything!

thanks
Will

 

[gaz_chops]

Hi welcome to the forum,

What formula are you currently using to return "Y" when all cells = "Y"

Gaz

 

[williamjtmarsh]

Hi Gaz,

=IF('Info Sheet'!AM4="Y","Y","")

But from what ive read, an IF, OR and AND combination is needed, but just cant get it right!

Thanks

 

[gaz_chops]

If the cells are only either Blank or contain "Y" or "NA", then try

=IF('Info Sheet'!AM4="","N","Y")

If the cells can have other data and you want to check if they have either "Y" or "NA", then try

=IF(OR('Info Sheet'!AM4={"Y","NA"}),"Y","N")

Gaz

 

[williamjtmarsh]

Hi Gaz,

Doesnt work, also I have multiple cells that would fit into that, for example the below doesn't work for two cells, so I added the AND, but still no luck

=IF(AND(OR('Info Sheet'!GF4={"Y","NA"},'Info Sheet'!GG4={"Y","NA"})),"Y","")

Also, when the cell fills in with "Y" it wont disappear when the date is removed from the 'info sheet'

Will

 

[gaz_chops]

Hi Will,

You are missing Parenthesis & 2nd OR statement

Try
=IF(AND(OR('Info Sheet'!GF4={"Y","NA"}),OR('Info Sheet'!GG4={"Y","NA"})),"Y","N")

Gaz

 

[williamjtmarsh]

Hi Gaz,

It is working, however I have to press the formula'd cell and press enter every time the cells have a change of information in

Is there a way of making it automatic?

Will

 

[gaz_chops]

Sounds like you have auto re-calc turned off.

If you press F9 does it work?

 

[williamjtmarsh]

IT WORKS! you legend! Thank you so much! I will be back if I have any issues, what a great forum! :D

 

[gaz_chops]

You're very welcome and yes it is a great forum.