Filling a formula down-need correct cell reference

[Guest]

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)

 

[Guest]

Simply remove he $ signs before the row numbers

 

[Rick Rothstein \(MVP - VB\)]

Those $ signs make a row or column absolute; without them, the row or column
is relative. Remove the $ sign from in front of the (row) numbers and I
think your formula will do what you want.

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

Rick

 

[Bernie Deitrick]

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

 

[Guest]

But then the formula stops working because it is not a match.

Simply remove he $ signs before the row numbers

--
Hth

Kassie Kasselman
Change xxx to hotmail

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)

 

[Guest]

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!

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

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)

 

[Bernie Deitrick]

Sorry,

You had left out a single quote that is required, and I missed it when I copied your formula:

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

(I'm also confused by the use of three workbooks: the one with the formula, worksheetname.xls and
worksheetname- John Brown-COPY.xls


Note that this formula should be entered without line breaks - the spaces / newsreader software /
browsers will probably mess with the line wrapping.

Excel doesn't care if the values are a mix of text and numbers or not - just whether they are the
same.


HTH,
Bernie
MS Excel MVP

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!

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

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)

 

[Guest]

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)

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!

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

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)

 

[Guest]

There are only two workbooks. The John Brown outside of the worksheet name is
the name of the tab.

Once I can get this running I have about 25 workbooks I have to do this with
and all referencing back to one master file. Is there a better way to do this?

Sorry for all the confusion but I appreciate the help.

Sorry,

You had left out a single quote that is required, and I missed it when I copied your formula:

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

(I'm also confused by the use of three workbooks: the one with the formula, worksheetname.xls and
worksheetname- John Brown-COPY.xls


Note that this formula should be entered without line breaks - the spaces / newsreader software /
browsers will probably mess with the line wrapping.

Excel doesn't care if the values are a mix of text and numbers or not - just whether they are the
same.


HTH,
Bernie
MS Excel MVP

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!

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


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)

 

[Bernie Deitrick]

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

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)

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!

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


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)

 

[Guest]

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.

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

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)

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!

:

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


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)

 

[Peo Sjoblom]

That would be hard using a formula given that there are 16777216 cells in
Excel 97 - 2003
and much more in Excel 2007, anything that big would probably choke Excel.


--


Regards,


Peo Sjoblom

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.

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


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)

:

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!

:

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


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)

 

[Bernie Deitrick]

That's a WHOLE LOT different!

In the future, it would be better if you described your problem with words
rather than posting a formula that you think may work. We can get almost
any formula to work - it just may not do what you think it should.

That said, try this, which will look for what must be a UNIQUE match in the
first 10000 rows of John Brown... If there are multiple matches, it
requires a different approach.

=INDEX('[JohnBrown.xls]John Brown'!$H:$H,SUMPRODUCT((A2='[JohnBrown.xls]John
Brown'!$A$2:$A$10000)*(B2='[JohnBrown.xls]John
Brown'!$B$2:$B$10000)*(C2='[JohnBrown.xls]John
Brown'!$C$2:$C$10000)*(D2='[JohnBrown.xls]John
Brown'!$D$2:$D$10000)*(E2='[JohnBrown.xls]John
Brown'!$E$2:$E$10000)*(F2='[JohnBrown.xls]John
Brown'!$F$2:$F$10000)*(G2='[JohnBrown.xls]John
Brown'!$G$2:$G$10000)*ROW('[JohnBrown.xls]John Brown'!$A$2:$A$10000)))


HTH,
Bernie
MS Excel MVP

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.

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


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)

:

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!

:

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


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)

 

[Guest]

It worked. Thanks a lot for the help.

That's a WHOLE LOT different!

In the future, it would be better if you described your problem with words
rather than posting a formula that you think may work. We can get almost
any formula to work - it just may not do what you think it should.

That said, try this, which will look for what must be a UNIQUE match in the
first 10000 rows of John Brown... If there are multiple matches, it
requires a different approach.

=INDEX('[JohnBrown.xls]John Brown'!$H:$H,SUMPRODUCT((A2='[JohnBrown.xls]John
Brown'!$A$2:$A$10000)*(B2='[JohnBrown.xls]John
Brown'!$B$2:$B$10000)*(C2='[JohnBrown.xls]John
Brown'!$C$2:$C$10000)*(D2='[JohnBrown.xls]John
Brown'!$D$2:$D$10000)*(E2='[JohnBrown.xls]John
Brown'!$E$2:$E$10000)*(F2='[JohnBrown.xls]John
Brown'!$F$2:$F$10000)*(G2='[JohnBrown.xls]John
Brown'!$G$2:$G$10000)*ROW('[JohnBrown.xls]John Brown'!$A$2:$A$10000)))


HTH,
Bernie
MS Excel MVP

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.

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


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)

:

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!

:

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


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)