=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found")
it tells me I am missing a parentheses

Yep...

=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot
Found")

If I want it to scan 4 workbooks simulataneously,
can I do that, by putting commas between the workbooks?

No, you can't do it like that.

You'd have to test each file as a separate condition:

=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+
SUMPRODUCT(--('[Workbook2.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+
SUMPRODUCT(--('[Workbook3.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)))+
SUMPRODUCT(--('[Workbook4.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","N ot
Found")

--
Biff
Microsoft Excel MVP


"grateful" wrote in message
...
Thank you so much for your help.

I'm sorry it has taken me so long to get back. I was off on a two week
course, and then was away during the holidays.

The solution for problem 1, worked perfectectly! Thank you!

For problem 2...I am going to work with it. Right now, it tells me I am
missing a parentheses, but if I add it to the end...it still doesn't work.

If you know what I am missing please let me know.

This is what I have:
=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found")

Also...If I want it to scan 4 workbooks simulataneously, can I do that, by
putting commas between the workbooks?

For
example?:=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range,[Workbook5.xls]SheetName'!Range,[Workbook6.xls]SheetName'!Range,[Workbook7.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found")


Can you still help me? :o)

Thank you!





"T. Valko" wrote:

1. That's how SEARCH handles empty cells. You could use an IF test to see
if
the cell is empty:

=IF(C4="","",SUMPRODUCT(--(ISNUMBER(SEARCH(C4............

If the cell is empty the formula returns a blank.

2. As long as the colon is a standard character in all the project names
in
book2 you can use a formula like this to "flag" the projects:

SubProject 1
MainProject:SubProject 1

=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","No t
Found")

--
Biff
Microsoft Excel MVP


"grateful" wrote in message
...
Hi, That worked beautifully! Thank you so much. This is what worked
for
me.
=SUMPRODUCT(--(ISNUMBER(SEARCH(C4,'[Workbook2.xls]Current'!$B$3:$B$146))),'[Workbook2.xls]Current'!$C$3:$C$146)

I have two questions now....I don't know if you can help me.

1) Not all the rows in the first work book have projects...so the
formula
is
actually returning the number "8752". Do you know why it's doing that?

2) Also is there a formula I could use in Work Book 2, to highlight all
the
projects that are not found in workbook 1?

Thank you so much!



"T. Valko" wrote:

One way:

=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,other_file_other_sheet!B1:B400 ))),other_file_other_sheet!C1:C400)

Have the other file open.
Start typing the formula:

=SUMPRODUCT(--(ISNUMBER(SEARCH(C1,

When you get that far use your mouse to point to the range(s) in the
other
file.

--
Biff
Microsoft Excel MVP


"grateful" wrote in message
...
Hi there.

I have two workbooks.

In the first workbook column C has about 200 subproject names.
(Ex. C
SubProject 1
SubProject 2
SubProject 3)



In the second workbook column B there are about 400 projects
(Ex. B C
MainProject:SubProject 1 10
Mainproject:SubProject 3 5
Mainproject:SubProject 2 20)


Those 200 projects are somewhere in the 400 projects listed in the
2nd
workbook.

I need a formula in Workbook 1 Column D to look for the sameproject
in
Workbook 2, column B, even though column B in workbook 2 contains
more
text
than Column C in workbook 1...and then put the corresponding value
into
Column D Worksheet 1 in the row with the matching project.

My difficulty is making an Index Formula that uses Match...when the
two
cells aren't IDENTICAL....I can use ISNUMBER...but that only gives
me a
True
or False...But I'm pretty sure I then need to INDEX...

Can anyone help?? :o)

Thank you so much!

You are all so smart.