| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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) |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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) |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically filling in a formula in a cell | Excel Discussion (Misc queries) | |||
| Copy formula down a column does not use correct cell reference | Excel Discussion (Misc queries) | |||
| copied formula has correct cell reference, but result of original | Excel Worksheet Functions | |||
| filling information from one cell and filling another. | Excel Worksheet Functions | |||
| How is it that a cell can transfer the correct formula, but the w. | Excel Discussion (Misc queries) | |||
Hybrid Mode
