Home |
Search |
Today's Posts |
#1
|
|||
|
|||
cells functioning odd
I have a huge excel sheet with 25000 lines it, i am using the following sum
if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. Here is an example of the problem: PART# VALUE A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED) The value in B1 does not want to display unless i go into A1 as if i were editing it and then, once the cursor starts blinking i just simply push ENTER to confirm the value in A1 and that is when the value in B1 appears. The values in the a coloumn A were imported as text because the first digit is a zero and if imported as general or as a number it looses the zero. I tried formating afterwards the cell value from text to number and it still doesn't work unless I manually go in and then push enter. There is so many parts it would take me days to go into each cell and push enter. Any help is appreciated. Much thanks in advance! |
#2
|
|||
|
|||
With that many lines in it, it is probably in recalc constant mode
but try =SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0) also what happens when you force a recalc (F9) it could be that you need to go to <tools<options<Calulation and verify that calculation is set for automatic. if the leading zeros disappear when you inport, I assume the data is totally numeric. You might also try a helper column with = value(trim(A1)) to reconvert to number. "Dan" wrote: I have a huge excel sheet with 25000 lines it, i am using the following sum if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. Here is an example of the problem: PART# VALUE A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED) The value in B1 does not want to display unless i go into A1 as if i were editing it and then, once the cursor starts blinking i just simply push ENTER to confirm the value in A1 and that is when the value in B1 appears. The values in the a coloumn A were imported as text because the first digit is a zero and if imported as general or as a number it looses the zero. I tried formating afterwards the cell value from text to number and it still doesn't work unless I manually go in and then push enter. There is so many parts it would take me days to go into each cell and push enter. Any help is appreciated. Much thanks in advance! |
#3
|
|||
|
|||
Now this doesn't help me at all because it looses the zeros, i need the zeros
there because 005 is unique format, because there is a 005 0005 and just 5, so i have to able to tell which value it is. "bj" wrote: With that many lines in it, it is probably in recalc constant mode but try =SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0) also what happens when you force a recalc (F9) it could be that you need to go to <tools<options<Calulation and verify that calculation is set for automatic. if the leading zeros disappear when you inport, I assume the data is totally numeric. You might also try a helper column with = value(trim(A1)) to reconvert to number. "Dan" wrote: I have a huge excel sheet with 25000 lines it, i am using the following sum if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. Here is an example of the problem: PART# VALUE A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED) The value in B1 does not want to display unless i go into A1 as if i were editing it and then, once the cursor starts blinking i just simply push ENTER to confirm the value in A1 and that is when the value in B1 appears. The values in the a coloumn A were imported as text because the first digit is a zero and if imported as general or as a number it looses the zero. I tried formating afterwards the cell value from text to number and it still doesn't work unless I manually go in and then push enter. There is so many parts it would take me days to go into each cell and push enter. Any help is appreciated. Much thanks in advance! |
#4
|
|||
|
|||
Dan wrote...
Now this doesn't help me at all because it looses the zeros, i need the zeros there because 005 is unique format, because there is a 005 0005 and just 5, so i have to able to tell which value it is. .... "Dan" wrote: I have a huge excel sheet with 25000 lines it, i am using the following sum if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. .... Replace the function above with =SUMIF(make!$A$2:$A$9571,finally!C1270,make!$C$2:$ C$9571) |
#5
|
|||
|
|||
I assume the (finally!C1270) cell is also text formatted
I still don't know why you have your problem but you can try the sumif Harlon recommended or =sumproduct(--(make!$A$2:$A$9571=finally!C1270),make!$C$2:$C$957 1) "Dan" wrote: Now this doesn't help me at all because it looses the zeros, i need the zeros there because 005 is unique format, because there is a 005 0005 and just 5, so i have to able to tell which value it is. "bj" wrote: With that many lines in it, it is probably in recalc constant mode but try =SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0) also what happens when you force a recalc (F9) it could be that you need to go to <tools<options<Calulation and verify that calculation is set for automatic. if the leading zeros disappear when you inport, I assume the data is totally numeric. You might also try a helper column with = value(trim(A1)) to reconvert to number. "Dan" wrote: I have a huge excel sheet with 25000 lines it, i am using the following sum if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. Here is an example of the problem: PART# VALUE A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED) The value in B1 does not want to display unless i go into A1 as if i were editing it and then, once the cursor starts blinking i just simply push ENTER to confirm the value in A1 and that is when the value in B1 appears. The values in the a coloumn A were imported as text because the first digit is a zero and if imported as general or as a number it looses the zero. I tried formating afterwards the cell value from text to number and it still doesn't work unless I manually go in and then push enter. There is so many parts it would take me days to go into each cell and push enter. Any help is appreciated. Much thanks in advance! |
#6
|
|||
|
|||
Ok, I got it to work, the reason I wasn't getting a resul is that, when I
formatted my cells one was a GENERAL and other was TEXT, now that I converted both to TEXT I get the correct result but the formula Harlan Grove gave does not give the correct resul instead of the value "1" I get "5" but all other formulas get the correct value. Thanks Guys "bj" wrote: I assume the (finally!C1270) cell is also text formatted I still don't know why you have your problem but you can try the sumif Harlon recommended or =sumproduct(--(make!$A$2:$A$9571=finally!C1270),make!$C$2:$C$957 1) "Dan" wrote: Now this doesn't help me at all because it looses the zeros, i need the zeros there because 005 is unique format, because there is a 005 0005 and just 5, so i have to able to tell which value it is. "bj" wrote: With that many lines in it, it is probably in recalc constant mode but try =SUM(IF(value(trim(make!$A$2:$A$9571))=finally!C12 70,make!$C$2:$C$9571,0),0) also what happens when you force a recalc (F9) it could be that you need to go to <tools<options<Calulation and verify that calculation is set for automatic. if the leading zeros disappear when you inport, I assume the data is totally numeric. You might also try a helper column with = value(trim(A1)) to reconvert to number. "Dan" wrote: I have a huge excel sheet with 25000 lines it, i am using the following sum if function: =SUM(IF(make!$A$2:$A$9571=finally!C1270,make!$C$2: $C$9571,0),0) entered with Ctrl+Shift+Enter. Here is an example of the problem: PART# VALUE A1. 025 B1. (THIS IS WHERE THE ABOVE FORMULA IS ENTERED) The value in B1 does not want to display unless i go into A1 as if i were editing it and then, once the cursor starts blinking i just simply push ENTER to confirm the value in A1 and that is when the value in B1 appears. The values in the a coloumn A were imported as text because the first digit is a zero and if imported as general or as a number it looses the zero. I tried formating afterwards the cell value from text to number and it still doesn't work unless I manually go in and then push enter. There is so many parts it would take me days to go into each cell and push enter. Any help is appreciated. Much thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help adding text values | Excel Worksheet Functions | |||
Convert data type of cells to Text,Number,Date and Time | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Heps to design Locked/Unlocked cells in protected worksheet | Excel Discussion (Misc queries) | |||
Convert data of cells to any type: Number, Date&Time, Text | Excel Discussion (Misc queries) |