Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
Hi,
This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
If you have selected a range and array entered...To edit or remove you will
need to select the same range and edit the formula in active cell and array enter again(Ctrl+Enter) This is not an answer to your query. You can rewrite your formula as a non-array one =SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman Sachs")*(Purchases!$D$2:$D$5000=DATE(2004,4,29)), Purchases!$I$2:$I$5000) If this post helps click Yes --------------- Jacob Skaria "Geoff Newham" wrote: Hi, This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
Jacob,
Thanks for the fast response. I have to admit to not understanding your explanation of what I need to do. I did copy and paste the suggested, changed formula and, what happened? The cell I was trying to change simply reverted to the previous formula - that is, it reverted to a replication of the cell above! -- Geoff "Jacob Skaria" wrote: If you have selected a range and array entered...To edit or remove you will need to select the same range and edit the formula in active cell and array enter again(Ctrl+Enter) This is not an answer to your query. You can rewrite your formula as a non-array one =SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman Sachs")*(Purchases!$D$2:$D$5000=DATE(2004,4,29)), Purchases!$I$2:$I$5000) If this post helps click Yes --------------- Jacob Skaria "Geoff Newham" wrote: Hi, This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
OK, so you want to look at a different investment firm.. Just change the name
from Goldman Sachs to Investec in the below SUMPRODUCT formula. The formula will look on the purchases tab of your workbook for the name "Investec", then verify the date in column D for those rows is at least April 4, 2009 and return the sum of your column I values in the matching rows. "Geoff Newham" wrote: Jacob, Thanks for the fast response. I have to admit to not understanding your explanation of what I need to do. I did copy and paste the suggested, changed formula and, what happened? The cell I was trying to change simply reverted to the previous formula - that is, it reverted to a replication of the cell above! -- Geoff "Jacob Skaria" wrote: If you have selected a range and array entered...To edit or remove you will need to select the same range and edit the formula in active cell and array enter again(Ctrl+Enter) This is not an answer to your query. You can rewrite your formula as a non-array one =SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman Sachs")*(Purchases!$D$2:$D$5000=DATE(2004,4,29)), Purchases!$I$2:$I$5000) If this post helps click Yes --------------- Jacob Skaria "Geoff Newham" wrote: Hi, This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
Thanks Sean. But, yes, I did all that and it still returned the Goldman stats.
I have since sorted it by working on a different cell and replicating the amended formula back across the 'traumatised' cell and it worked. It would be good to know how to prevent this pop up message recurring by knowing what to avoid doing in future. Our in-house Actuary couldn't figure it out, either! -- Geoff "Sean Timmons" wrote: OK, so you want to look at a different investment firm.. Just change the name from Goldman Sachs to Investec in the below SUMPRODUCT formula. The formula will look on the purchases tab of your workbook for the name "Investec", then verify the date in column D for those rows is at least April 4, 2009 and return the sum of your column I values in the matching rows. "Geoff Newham" wrote: Jacob, Thanks for the fast response. I have to admit to not understanding your explanation of what I need to do. I did copy and paste the suggested, changed formula and, what happened? The cell I was trying to change simply reverted to the previous formula - that is, it reverted to a replication of the cell above! -- Geoff "Jacob Skaria" wrote: If you have selected a range and array entered...To edit or remove you will need to select the same range and edit the formula in active cell and array enter again(Ctrl+Enter) This is not an answer to your query. You can rewrite your formula as a non-array one =SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman Sachs")*(Purchases!$D$2:$D$5000=DATE(2004,4,29)), Purchases!$I$2:$I$5000) If this post helps click Yes --------------- Jacob Skaria "Geoff Newham" wrote: Hi, This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Can't change part of an array
If you delete all your SUM formulas and go with SUMPRODUCT, you won't receive
thsi error in the future... Presumably, your SUM formula spanned multiple cells, and it appears you were attempting to make a change to one cell first, which most likely caused the error. Can always just delete all SUM cells except the current one, then make the change. "Geoff Newham" wrote: Thanks Sean. But, yes, I did all that and it still returned the Goldman stats. I have since sorted it by working on a different cell and replicating the amended formula back across the 'traumatised' cell and it worked. It would be good to know how to prevent this pop up message recurring by knowing what to avoid doing in future. Our in-house Actuary couldn't figure it out, either! -- Geoff "Sean Timmons" wrote: OK, so you want to look at a different investment firm.. Just change the name from Goldman Sachs to Investec in the below SUMPRODUCT formula. The formula will look on the purchases tab of your workbook for the name "Investec", then verify the date in column D for those rows is at least April 4, 2009 and return the sum of your column I values in the matching rows. "Geoff Newham" wrote: Jacob, Thanks for the fast response. I have to admit to not understanding your explanation of what I need to do. I did copy and paste the suggested, changed formula and, what happened? The cell I was trying to change simply reverted to the previous formula - that is, it reverted to a replication of the cell above! -- Geoff "Jacob Skaria" wrote: If you have selected a range and array entered...To edit or remove you will need to select the same range and edit the formula in active cell and array enter again(Ctrl+Enter) This is not an answer to your query. You can rewrite your formula as a non-array one =SUMPRODUCT((Purchases!$F$2:$F$5000="Goldman Sachs")*(Purchases!$D$2:$D$5000=DATE(2004,4,29)), Purchases!$I$2:$I$5000) If this post helps click Yes --------------- Jacob Skaria "Geoff Newham" wrote: Hi, This has been particulary galling and it's not the first time, either! I had this message: "Can't change part of an array". It popped up while dealing with some corrections to a sheet. I was left with no option but to ctrl+alt+delete to end the task since I could not click on any part of the sheet or other worksheets nor could I even delete the sheet or worksbook and most functions are greyed out. The formula was ={SUM(IF(Purchases!$F$2:$F$5000="Goldman Sachs",IF(Purchases!$D$2:$D$5000=DATEVALUE("29/04/2004"),Purchases!$I$2:$I$5000,0),0))}. Since I discovered that this actually replicated the cell above and should have been analysing purchases via Investec, not Goldman Sachs, I changed the formula to read Investec. This is one of 25 different brokers in a list where each one analyses the purchases using the above formula. The system baulked at the first change and so I deleted the whole formula to re-write it, then the notice popped up. Q1: If this notice pops up again, is there any way to back out of it without losing everything? Q2: What causes this notice to pop up? If I understand that, I can avoid the cause. Many thanks Geoff |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"Cannot change part of an array!" | Excel Worksheet Functions | |||
Array help Part 2 | Excel Worksheet Functions | |||
Cannot chg. part of an array | Excel Worksheet Functions | |||
warning: cannot change part of an array. how do I by pass this? | Excel Worksheet Functions | |||
What instead of an array formula part 2 | Excel Discussion (Misc queries) |