IF statement or VLOOKUP - text string

[f499]

Not sure if I should be using an IF function, a VLOOKUP function, or a combination of both...

I have two worksheets that serve as raw data. I have created a summary worksheet, which I am trying to lookup data from the data worksheets.

Worksheet 1
Cell A1 = ABC 01
Cell A2 = Blank

Worksheet 2
Cell A1 = Blank
Cell A2 = ABC 99

Worksheet 3 (Summary)
Cell A1
I want to lookup cell A1 in worksheet 1 and see if it contains the text string ''ABC''. If it contains the text string ''ABC'' (in this case, it does), then I want the value in cell A1 in worksheet 1 to be returned (in this case =''ABC 01''). If the test doesn't work, I want to check the value in cell A1 in worksheet 2.

Worksheet 3 (Summary)
Cell A2
Same thing, but, since cell A2 in worksheet 1 is blank, I want Excel to check cell A2 in worksheet 2. Since A2 in worksheet 2 contains the text string ''ABC'', I want the value in cell A2 in worksheet 2 to be returned (in this case = ''ABC 99'')

I tried the following IF statement:
=IF(Worksheet1!A1="ABC",Worksheet1!A1,IF(Worksheet2!A1="ABC", Worksheet2!A1,FALSE))

Problem is, the text has to be an exact match. I am looking for a match within a text string (ABC works with ABC 01, ABC 99, or ABC123, etc...)

Your help is greatly appreciated.

If my scenario is ambiguous...let me know, and I'll try another example!

Thanks in advance,

F499

 

[VoG]

Try this http://www.ozgrid.com/VBA/VlookupAllSheets.htm

 

[f499]

Thanks for the input.

I've copied the code in a module, but I am getting an error message. As well, once pasted in the module, some of the lines/text are in red. I'm not a VBA expert....sorry.

This is how it got pasted in the module. I'm suspecting it is how the code got pasted in.

F499



Function VLOOKAllSheets(Look_Value As Variant, Tble_Array As Range, _
Col_num as Integer, Optional Range_look as Boolean)

''''''''''''''''''''''''''''''''''''''''''''''''
'Written by OzGrid.com

'Use VLOOKUP to Look across ALL Worksheets and stops _
at the first match found.
'''''''''''''''''''''''''''''''''''''''''''''''''
Dim wSheet As Worksheet
Dim vFound

On Error Resume Next

For Each wSheet In ActiveWorkbook.Worksheets
With wSheet
Set Tble_Array = .Range(Tble_Array.Address)
vFound = WorksheetFunction.VLookup _
(Look_Value, Tble_Array, _
Col_num, Range_look)
End With
If Not IsEmpty(vFound) Then Exit For
Next wSheet

Set Tble_Array = Nothing
VLOOKAllSheets = vFound
End Function

 

[markmzz]

If I understand correctly what you want, maybe this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 01</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 99</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">FALSE</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=IF(<font color="Blue">ISERR(<font color="Red">SEARCH(<font color="Green">"ABC",Sheet1!$A1</font>)</font>),IF(<font color="Red">ISERR(<font color="Green">SEARCH(<font color="Purple">"ABC",Sheet2!$A1</font>)</font>),FALSE,Sheet2!$A1</font>),Sheet1!$A1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[f499]

Thanks Markmzz. I think I prefer that method.

Although, what happens if I add a sheet (example, 4 data sheets). Can you help me out with the revised function?

Thanks in advance,

F499

 

[markmzz]

Thanks Markmzz. I think I prefer that method.

Although, what happens if I add a sheet (example, 4 data sheets). Can you help me out with the revised function?

Thanks in advance,

F499

Try this function (with a copy of your Workbook):

Function SearchAllSheets(Look_Value As Variant, Look_Cell As Range) As Variant
    Dim mySheet As Worksheet, myCell As Range, myPos As Variant
 
    Application.Volatile True
 
    On Error Resume Next
    For Each mySheet In ActiveWorkbook.Worksheets
        If mySheet.Name <> "Master" Then
            Set myCell = mySheet.Range(Look_Cell.Address)
            myPos = WorksheetFunction.Search(Look_Value, myCell)
            If Not IsEmpty(myPos) Then Exit For
        End If
    Next mySheet
    SearchAllSheets = IIf(Not IsEmpty(myPos), myCell, False)
    Set myCell = Nothing
End Function

Results:

Excel 2007<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">ABC 01</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2">ABC 01</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">ABC 99</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2">ABC 99</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #c5d9f1">FALSO</TD><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #f2f2f2">FALSO</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right"></TD><TD style="TEXT-ALIGN: right"></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: center">********</TD><TD style="TEXT-ALIGN: center">********</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD>Formula</TD><TD>Function</TD></TR></TBODY></TABLE>Master


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>A1</TH><TD style="TEXT-ALIGN: left">=IF(ISERR(SEARCH("ABC",Sheet1!$A1)),IF(ISERR(SEARCH("ABC",Sheet2!$A1)),FALSE,Sheet2!$A1),Sheet1!$A1)</TD></TR><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>B1</TH><TD style="TEXT-ALIGN: left">=SearchAllSheets("ABC",A1)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Markmzz

 

[f499]

Thanks again Markmzz.

I added the code as a module in VB. When I have the function in a couple of cells, works great. As soon as I copy it accross to other rows/cells, the worksheet goes into a continuous "calculating" mode. The worksheet becomes unusable. It eventually comes back with a circular reference (not sure why). Am I doing something wrong?

My preference was to stay out of VB Editor. Your initial IF/ISERR/SEARCH function worked nice with 2 data sheets. If there is a way to have it work with 4 data sheets, that would be great. I tried modifying your initial function, but no luck.

Either way, as long as I can get something to work...

Thanks for your help and guidance, again.

F499

 

[markmzz]

Thanks again Markmzz.

I added the code as a module in VB. When I have the function in a couple of cells, works great. As soon as I copy it accross to other rows/cells, the worksheet goes into a continuous "calculating" mode. The worksheet becomes unusable. It eventually comes back with a circular reference (not sure why). Am I doing something wrong?

My preference was to stay out of VB Editor. Your initial IF/ISERR/SEARCH function worked nice with 2 data sheets. If there is a way to have it work with 4 data sheets, that would be great. I tried modifying your initial function, but no luck.

Either way, as long as I can get something to work...

Thanks for your help and guidance, again.

F499

Try this:

Note1: for to use the function you need a Master worksheet.

Note2: here the function works normal.

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 013</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 013</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 013</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 994</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 994</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 994</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 100</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 100</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 100</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 03</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 03</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 03</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;background-color: #C5D9F1;;">FALSO</td><td style="text-align: center;background-color: #F2F2F2;;">FALSO</td><td style="text-align: center;background-color: #F2F2F2;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 04</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 04</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 04</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 102</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 102</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 102</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 103</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 103</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 103</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 06</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 06</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 06</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 07</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 07</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 07</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 105</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 105</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 105</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">16</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 106</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 106</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 106</td></tr><tr ><td style="color: #161120;text-align: center;">17</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 09</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 09</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 09</td></tr><tr ><td style="color: #161120;text-align: center;">18</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">19</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 10</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 10</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 10</td></tr><tr ><td style="color: #161120;text-align: center;">20</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 108</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 108</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 108</td></tr><tr ><td style="color: #161120;text-align: center;">21</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">22</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 109</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 109</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 109</td></tr><tr ><td style="color: #161120;text-align: center;">23</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 12</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 12</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 12</td></tr><tr ><td style="color: #161120;text-align: center;">24</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">25</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 13</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 13</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 13</td></tr><tr ><td style="color: #161120;text-align: center;">26</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 111</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 111</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 111</td></tr><tr ><td style="color: #161120;text-align: center;">27</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">28</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 112</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 112</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 112</td></tr><tr ><td style="color: #161120;text-align: center;">29</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 15</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 15</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 15</td></tr><tr ><td style="color: #161120;text-align: center;">30</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">31</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 16</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 16</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 16</td></tr><tr ><td style="color: #161120;text-align: center;">32</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 114</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 114</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 114</td></tr><tr ><td style="color: #161120;text-align: center;">33</td><td style="text-align: center;background-color: #C5D9F1;;">FALSO</td><td style="text-align: center;background-color: #F2F2F2;;">FALSO</td><td style="text-align: center;background-color: #F2F2F2;;">FALSO</td></tr><tr ><td style="color: #161120;text-align: center;">34</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 115</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 115</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 115</td></tr><tr ><td style="color: #161120;text-align: center;">35</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 18</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 18</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 18</td></tr><tr ><td style="color: #161120;text-align: center;">36</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">37</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 19</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 19</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 19</td></tr><tr ><td style="color: #161120;text-align: center;">38</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 117</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 117</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 117</td></tr><tr ><td style="color: #161120;text-align: center;">39</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 012</td></tr><tr ><td style="color: #161120;text-align: center;">40</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 118</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 118</td><td style="text-align: center;background-color: #F2F2F2;;">ABC 118</td></tr><tr ><td style="color: #161120;text-align: center;">41</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">42</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td><td style="text-align: center;;">********</td></tr><tr ><td style="color: #161120;text-align: center;">43</td><td style=";">Formula</td><td style=";">Function</td><td style=";">Function</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Master</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A1</th><td style="text-align:left">=IF(<font color="Blue">ISERR(<font color="Red">SEARCH(<font color="Green">"ABC",Sheet1!$A1</font>)</font>),
IF(<font color="Red">ISERR(<font color="Green">SEARCH(<font color="Purple">"ABC",Sheet2!$A1</font>)</font>),
IF(<font color="Green">ISERR(<font color="Purple">SEARCH(<font color="Teal">"ABC",Sheet3!$A1</font>)</font>),
IF(<font color="Purple">ISERR(<font color="Teal">SEARCH(<font color="#FF00FF">"ABC",Sheet4!$A1</font>)</font>),FALSE,Sheet4!$A1</font>),Sheet3!$A1</font>),Sheet2!$A1</font>),Sheet1!$A1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B1</th><td style="text-align:left">=SearchAllSheets(<font color="Blue">"ABC",$A1</font>)</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C1</th><td style="text-align:left">=SearchAllSheets(<font color="Blue">"ABC",$A1</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[TYOzzy]

If I understand correctly what you want, maybe this can help you:

Excel 2007<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6" cellpadding="2.5px"><colgroup><col style="background-color: #E0E0F0" width="25px"><col></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th>
</th><th>A</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 01</td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;background-color: #C5D9F1;;">ABC 99</td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;background-color: #C5D9F1;;">FALSE</td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;">
</td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">********</td></tr></tbody></table>

Sheet3​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" width="85%" cellpadding="2.5px"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6" width="100%" cellpadding="2.5px"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #E0E0F0;color: #161120" width="10px">A1</th><td style="text-align:left">=IF(ISERR(SEARCH("ABC",Sheet1!$A1)),IF(ISERR(SEARCH("ABC",Sheet2!$A1)),FALSE,Sheet2!$A1),Sheet1!$A1)</td></tr></tbody></table></td></tr></tbody></table>

Markmzz

This looks very close to what I need. Would you mind taking a look at this link?

http://www.mrexcel.com/forum/showthread.php?t=583496

Thanks so much!