Checking

[fatalcore]

Hi I have an excel file with lots of tabs, now the column B of all the tabs contains SA or TA. Is there a way to generate a sheet with the tab name and the value of column B.

For example:
Sheet Value
TA001 SA
TA002 Both (As SA & TA are both present)
CO007 Both
TA009 TA

I am attaching an extract of my data for your reference.Please guide me,
thanks,me

[mikerickson]

If you have the sheet names in column A of your summary sheet, you could use this formula and drag down.
=CHOOSE(SIGN(COUNTIF(INDIRECT(A1&"!B:B"),"SA"))+2*SIGN(COUNTIF(INDIRECT(A1&"!B:B"),"TA")) +1, "neither", "SA","TA", "both")

[fatalcore]

Amazing & perfect, thanks a ton mikerickson,
Can you kindly make a vba code do the same thing as the file is a system generated file and there are many sheets, and I don't have the summary sheet with all the names populated.
a vba code than can do the same thing will be a great help & also I can use it in future.
Thanks a ton ! :D Godbless

[fatalcore]

Any help thanks friends.

[mikerickson]

Dim ws As Worksheet, oneSheet as Worksheet

With ThisWorkbook
    Set ws = .Worksheets("Summary")
    ws.Range("A:A").ClearContents
    ws.Range("A1:B1").Value = Array("Sheet Name", "Status")

    For Each oneSheet in .Worksheets
        if oneSheet.Name <> ws.Name Then
            ws.Range("A1000").End(xlUp).Offset(0,1).Value = oneSheet.Name
        End If
    Next oneSheet
    With ws
        With Range(.Cells(2, 1), .Cells(.Rows.Count,1).End(xlup)).Offset(0, 1)
            .FormulaR1C1 = "=CHOOSE(SIGN(COUNTIF(INDIRECT(RC1&""!B:B""),""SA""))+2*SIGN(COUNTIF(INDIRECT(RC1&""!B:B""),""TA"")) +1, ""neither"", ""SA"",""TA"", ""both"")"
        End With
    End With
End With