| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
formula that works in a different workbook
Hello folks I have a formula here that works in another work book just fine
the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula that works in a different workbook
Sounds like an array formula.
Enter the formula into the cell you want, having made the edits you said below. Press Contol+Shift+Enter (CSE). Should work. Jude Reason wrote: Hello folks I have a formula here that works in another work book just fine the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula that works in a different workbook
Not sure what the problem is but the {} suggest the formula is entered as an
array formula. Try this non-array version =SUMPRODUCT(--('WELD LOG'!$H$8:$H$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$I$8:$I$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$J$8:$J$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$K$8:$K$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW")) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jude Reason" wrote in message ... Hello folks I have a formula here that works in another work book just fine the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula that works in a different workbook
or even
=SUMPRODUCT((('WELD LOG'!$H$8:$H$2043=C15)+('WELD LOG'!$I$8:$I$2043=C15)+('WELD LOG'!$J$8:$J$2043=C15)+('WELD LOG'!$K$8:$K$2043=C15))* ('WELD LOG'!$F$8:$F$2043="SW")) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Bob Phillips" wrote in message ... Not sure what the problem is but the {} suggest the formula is entered as an array formula. Try this non-array version =SUMPRODUCT(--('WELD LOG'!$H$8:$H$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$I$8:$I$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$J$8:$J$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW"))+ SUMPRODUCT(--('WELD LOG'!$K$8:$K$2043=C15),--('WELD LOG'!$F$8:$F$2043="SW")) -- HTH Bob Phillips (there's no email, no snail mail, but somewhere should be gmail in my addy) "Jude Reason" wrote in message ... Hello folks I have a formula here that works in another work book just fine the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula that works in a different workbook
Well what do ya know it worked. All the headache for Control+Shift+Enter.
Guess thats what I get for not finishing my video professor. HAHA -- Jude Reason CWI #06041451 "bridgesmj" wrote: Sounds like an array formula. Enter the formula into the cell you want, having made the edits you said below. Press Contol+Shift+Enter (CSE). Should work. Jude Reason wrote: Hello folks I have a formula here that works in another work book just fine the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
formula that works in a different workbook
=SUMPRODUCT(('WELD LOG'!$F$8:$F$2043="SW")*(ISNUMBER(SEARCH(C15,'WELD
LOG'!$H$8:$K$2043)))) "Jude Reason" wrote: Hello folks I have a formula here that works in another work book just fine the only difference in these two formulas is the column that they reference and these {} symbols when I click on the cell that they represent. here is the formula. =SUM(IF('WELD LOG'!$H$8:$H$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$I$8:$I$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$J$8:$J$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0)))+SUM(IF('WELD LOG'!$K$8:$K$2043=C15,IF('WELD LOG'!$F$8:$F$2043="SW",1,0))) also where the "SW" is substitute that with "WOL" also in about 5 cells the #value shows up and the rest of the cells just show up 0. Like I said in the other workbook this formula works fine. I would appreciate any help as I have pretty much exhausted myself trying to figure this one out. -- Jude Reason CWI #06041451 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Relative vs Absolute referencing of Workbooks | Excel Worksheet Functions | |||
| Formula Partially Works, Please Help | Excel Worksheet Functions | |||
| Protect Workbook vs Worksheet?? | Excel Worksheet Functions | |||
| How do I copy a cell + it's formula from one workbook to another? | Excel Discussion (Misc queries) | |||
| Formula checking multiple worksheets | Excel Worksheet Functions | |||
Linear Mode
