The I used the formula where I removed the $ just before the numbers.
So the rows increment but how can I get the formula to search the entire
Johnbrown worksheet, not just in those rows. The two workbooks don't have
matching cell/rows. The match could be anywhere in the spreadsheet.

Thanks alot for the help.

"Bernie Deitrick" wrote:

Either remove all the $

=IF(SUMPRODUCT(($A2:$G2='[Johnbrown.xls]John Brown'!$A$2:$G$2)*1) =7,'[Johnbrown.xls]John
Brown'!$H$2,FALSE)

becomes

=IF(SUMPRODUCT((A2:G2='[Johnbrown.xls]John Brown'!A2:G2)*1) =7,'[Johnbrown.xls]John Brown'!H2,FALSE)

or just the $s before the row numbers

=IF(SUMPRODUCT(($A2:$G2='[Johnbrown.xls]John Brown'!$A2:$G2)*1) =7,'[Johnbrown.xls]John
Brown'!$H2,FALSE)


HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
I tried the formula but the cell references still don't increment after the
first set .
Any suggestions?

=IF(SUMPRODUCT(($A2:$G2='[Johnbrown.xls]John Brown'!$A$2:$G$2)*1)
=7,'[Johnbrown.xls]John Brown'!$H$2,FALSE)

"NeedExcelHelp07" wrote:

ok. in cells are a mix between text and numeric characters. Does that change
the formula below, because I keep getting an error message.

Thanks!

"Bernie Deitrick" wrote:

You actually need more help than just the address incrementing.

Your formula will only look at the first value, in column A, for the comparison. You need to
use

=IF(SUMPRODUCT(($A2:$G2='[worksheetname.xls]John Brown!$A2:$G2)*1)=7,'[worksheetname.xls]John
Brown!$H2,FALSE)

to compare all seven values in column A through G.

HTH,
Bernie
MS Excel MVP


"NeedExcelHelp07" wrote in message
...
Below is the formula I'm using to for one worksheet to match data with a
worksheet in another workbook, if data matches it references to a cell on
that row, if it doesn't it returns false. On the master file when I fill the
below formula down on the first A2:G2 increases while the others stay the
same. How can I fix this so that they all increase when I fill down?

Thanks

=IF($A2:$G2='[worksheetname.xls]John Brown!$A$2:$G$2,'[worksheetname- John
Brown-COPY.xls]John Brown!$H$2,FALSE)