|
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
Simply remove he $ signs before the row numbers
-- Hth Kassie Kasselman Change xxx to hotmail "NeedExcelHelp07" wrote: 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) |
Filling a formula down-need correct cell reference
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 "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) |
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
But then the formula stops working because it is not a match.
"Kassie" wrote: Simply remove he $ signs before the row numbers -- Hth Kassie Kasselman Change xxx to hotmail "NeedExcelHelp07" wrote: 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) |
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
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 "NeedExcelHelp07" wrote in message ... 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) |
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
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. "Bernie Deitrick" wrote: 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 "NeedExcelHelp07" wrote in message ... 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) |
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
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) |
Filling a formula down-need correct cell reference
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 "NeedExcelHelp07" wrote in message ... 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) |
Filling a formula down-need correct cell reference
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 "NeedExcelHelp07" wrote in message ... 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) |
Filling a formula down-need correct cell reference
It worked. Thanks a lot for the help.
"Bernie Deitrick" wrote: 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 "NeedExcelHelp07" wrote in message ... 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) |
| All times are GMT +1. The time now is 04:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com