Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet formula incorrect after copying to another worksheet
I have a formula in one workbook :
=COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) ) When i copied this formula into another workbook, the answer becomes "1". The answer in the original worksheet is "19". How can I get this corrected? Thanks |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet formula incorrect after copying to another worksheet
--In edit mode F2, paste the formula to a cell and instead of just Enter
press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" --You can use the below alternative (enter normally) =SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")* (Sheet1!$N$2:$N$180=1)) If this post helps click Yes --------------- Jacob Skaria "Karen" wrote: I have a formula in one workbook : =COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) ) When i copied this formula into another workbook, the answer becomes "1". The answer in the original worksheet is "19". How can I get this corrected? Thanks |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Worksheet formula incorrect after copying to another worksheet
Sorry, I clicked the "no" button wrongly. Actually its yes and no.
1. I tried pressing CTRL+SHIFT+ENTER and saw the curly braces at both ends of the formula but it doesn't work. So I tried the other option. 2. =SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")*(Sheet1!$N$2:$N$180=1)) works!! But when i enter the mmyy as "1009", it reflected "0" which is incorrect. It there something wrong with the "mmyy" function? Thank you again. "Jacob Skaria" wrote: --In edit mode F2, paste the formula to a cell and instead of just Enter press CTRL+SHIFT+ENTER to enter the formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}" --You can use the below alternative (enter normally) =SUMPRODUCT((TEXT(Sheet1!$B$2:$B$180,"mmyy")="0109 ")* (Sheet1!$N$2:$N$180=1)) If this post helps click Yes --------------- Jacob Skaria "Karen" wrote: I have a formula in one workbook : =COUNT(IF((Sheet1!$B$2:$B$180=DATEVALUE("Jan-09"))*(Sheet1!$N$2:$N$180=1),Sheet1!$A$2:$A$180) ) When i copied this formula into another workbook, the answer becomes "1". The answer in the original worksheet is "19". How can I get this corrected? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying formula into another worksheet | Excel Worksheet Functions | |||
Copying a formula from one worksheet to another worksheet | Excel Worksheet Functions | |||
Looking up a variable in one worksheet and copying information from another column to another worksheet?? | Excel Discussion (Misc queries) | |||
Same formula referencing same cells returns incorrect results, randomly, when pasted into new worksheet | Excel Worksheet Functions | |||
Copying a worksheet witrh protected cells to a new worksheet | Excel Worksheet Functions |