Need a IF Formula for across Tabs, Can someone please help?

[Loominal]

In Short,
Is there a command that can know if B2 through B10 (yes 9 boxes have to have text in them) on Tab2 has "text" or and "X" in it.. then Tab1, box "J14" can say, "Completed".



=IF(ISTEXT('TAB2'!B2:B10), "X", IF(AND(ISTEXT('TAB2'!B2),ISTEXT(!B3)),"","X"))

I'm not sure i know what i'm talking about here. Much help would be appreciated.
To also clarify, if B2 through B10 has partial X's in it i still don't want J14 to have an X.
So if B2, B3, B4, B5 all have text or X's. I don't want J14 to say anything Till ALL 9 rows have been completed. Is that done with OR's?? or more If's?

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation and some expected results.

[Glenn Kennedy]

Forget it... it was easy to visualise:

=IF(ROWS(FILTER('Tab2'!B2:B10,'Tab2'!B2:B10<>""))=9,"Complete","")

[Loominal]

wow thank you so much. I was just in the process of attaching a work book.. but yours is correct. !!! Thank you so much

If I wanted to do this as a ROW across , would this change at all? B2 through J2 ??

=IF(ROW(FILTER('Tab2'!B2:J2,'Tab2'!B2:J2<>""))=9,"Complete","") ??

[Pete_UK]

A couple of alternatives:

=IF(COUNTIF('Tab2'!B2:B10,"<>")=9,"Completed","")

or:

=IF(COUNTA('Tab2'!B2:B10)=9,"Completed","")

Hope this helps.

Pete

[Glenn Kennedy]

You're welcome! It is ALWAYS easier if we have a sheet to play with. Words are frequently ambiguous.

[Loominal]

agreed. I did edit my post prior, mind taking a look Glenn?

[Loominal]

A couple of alternatives:

=IF(COUNTIF('Tab2'!B2:B10,"<>")=9,"Completed","")

or:

=IF(COUNTA('Tab2'!B2:B10)=9,"Completed","")

Hope this helps.

Pete

Thanks Pete!! That is very helpful. Question though is "COUNTIF or COUNTA" only used for numbers?? or can I use "X" in those fields? It's basically checking off a box and then on the front page saying that item and all 9 steps are done.

[Glenn Kennedy]

One minor tweak:

=IF(COLUMNS(FILTER('Tab2'!B2:J2,'Tab2'!B2:J2<>""))=9,"Complete","")

[Pete_UK]

No, the COUNT function is only for numbers - COUNTA is for count-all (i.e. non-blanks) and the COUNTIF can also be used for both text and numbers as it uses the Text Comparison routine in Excel. Here's another:

=IF(COUNTIF('Tab2'!B2:B10,"?*")=9,"Completed","")

which uses the wildcard characters, in this case meaning at least one character ( ? ) and any number of other characters ( * ).

Hope this helps, and thanks for the rep.

Pete