Multiple sheet text.

[SunDesignJoe]

I am using the following formula to check three different sheets for text in a particular cell. I would like an easier way to do this so that I can add additional sheets and continue to search all of them for text without continuing a really long formula.

=IF(Sheet2!A1<>"",Sheet2!A1,IF(Sheet1!A1<>"",Sheet1!A1,IF(Sheet4!A1<>"",Sheet4!A1,"")))

 

[SunDesignJoe]

I see several people have viewed this but noone seems to have an answer so I am posting this to recirculate my request. Any help would be greatly appreciated.

 

[fboehlandt]

Not without VBA you can't. I recommend something on the lines of:

Option Base 1
Sub RotateWs()
Dim wb As Workbook
Dim ws As Worksheet
ReDim Mytest(1)
Set wb = ThisWorkbook
c = 0
For Each ws In wb.Worksheets
    c = c + 1
    ReDim Preserve Mytest(c)
    Mytest(c) = ws.Range("A1").Value
    If c > 1 Then
        If Mytest(c) <> Mytest(c - 1) Then
            MsgBox "At least two values differ"
            GoTo EndSub
        End If
    End If
Next ws
MsgBox "All values are identical"
Exit Sub
EndSub:
End Sub