IF Formula for Specific Cell Value Across Multiple Worksheets

[kschmit1]

Good morning!

I am working on a monthly reporting file where I have multiple worksheets labeled 1-31 for each day of the month. I have successfully rolled up all data points from these worksheets except for one.

Cell F22 on all 31 sheets is a yes/no drop-down. When I need to roll-up at the monthly level is - if any F22s on worksheets 1:31 = "No", then return a response of "No", if false then "Yes"

So in other words if F22 on all sheets is Yes, then this will populate Yes on my monthly roll-up. If any of the worksheets have No in F22, then the monthly will say No. Not having much luck getting this to work, can anyone assist? Trying to avoid having to input a formula that references each sheet individually for cell F22.

[63falcondude]

It it always more difficult building formulas that refer to multiple sheets than it would be if the data was on one sheet.

That being said, you can look into creating a 3D countif formula.

Try this:

=IF(SUMPRODUCT(COUNTIF(INDIRECT(ROW(1:31)&"!F22"),"No")),"No","Yes")

[kschmit1]

thank you! Works perfect!

[63falcondude]

You're welcome. Happy to help.