Hi Again....
So I think it's working now! Thank you very much.
I was wondering if you could tell me what the +1,50 is referring to?
Just
so I can understand.
Thank you.
:
=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50)),"","Not
Found")
it tells me I am missing a parentheses
Yep...
=IF(SUMPRODUCT(--('[Workbook1.xls]SheetName'!Range=MID(B3,FIND(":",B3)+1,50))),"","Not
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))),"","Not
Found")
--
Biff
Microsoft Excel MVP
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)),"","Not
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)),"","Not
Found")
Can you still help me?
)
Thank you!
:
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)),"","Not
Found")
--
Biff
Microsoft Excel MVP
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!
:
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
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??
)
Thank you so much!
You are all so smart.
