Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Excel Error when copying formulas
I have these array formulas that I want to copy down to other rows
=IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I $115:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$11 5:$BI$417,0)-1,18,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I$11 5:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$115:$ BI$417,0)-1,18,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,7,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,7,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,35,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,35,-1,-1)) These three formulas are repeated in the above order between columns BK3 and FQ79 but I need to copy them down to 480. When I copy them one at a time it seems to copy fine but when I try to select multiple cells I get the following error messsage and Excel has to close. "Microsoft Excel for Windows has encountered a problem and needs to close. We are sorry for the inconvenience." Does anyone know of a fix or how to rewrite the formulas so it doesn't bomb out. Thanks for your help Joe |
#2
|
|||
|
|||
What happens if you try to copy just the MATCH() component of each formula?
Since you've got a heavy-duty MATCH() component twice in each formula....I'm just guessing, but that's the step I'd try first "Joe Gieder" wrote: I have these array formulas that I want to copy down to other rows =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I $115:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$11 5:$BI$417,0)-1,18,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I$11 5:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$115:$ BI$417,0)-1,18,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,7,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,7,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,35,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,35,-1,-1)) These three formulas are repeated in the above order between columns BK3 and FQ79 but I need to copy them down to 480. When I copy them one at a time it seems to copy fine but when I try to select multiple cells I get the following error messsage and Excel has to close. "Microsoft Excel for Windows has encountered a problem and needs to close. We are sorry for the inconvenience." Does anyone know of a fix or how to rewrite the formulas so it doesn't bomb out. Thanks for your help Joe |
#3
|
|||
|
|||
Thanks for the response.
I chaned the formulas to only have the first MATCH() portion =MATCH($I3&BK$1&$BG3&$BI3,$I$115:$I$417&$AN$115:$A N$417&$BG$115:$BG$417&$BI$115:$BI$417,0) =MATCH($I3&BK$1,$I$115:$I$488&$AN$115:$AN$488,0) =MATCH($I3&BK$1,$I$115:$I$488&$AN$115:$AN$488,0) and copied the formulas down and it still gave me the same error. Different results but same error. Joe "Duke Carey" wrote: What happens if you try to copy just the MATCH() component of each formula? Since you've got a heavy-duty MATCH() component twice in each formula....I'm just guessing, but that's the step I'd try first "Joe Gieder" wrote: I have these array formulas that I want to copy down to other rows =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I $115:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$11 5:$BI$417,0)-1,18,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1&$BG3&$BI3,$I$11 5:$I$417&$AN$115:$AN$417&$BG$115:$BG$417&$BI$115:$ BI$417,0)-1,18,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,7,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,7,-1,-1)) =IF(ISNA(OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$48 8&$AN$115:$AN$488,0)-1,35,-1,-1)),0,OFFSET($I$115,MATCH($I3&BK$1,$I$115:$I$488&$ AN$115:$AN$488,0)-1,35,-1,-1)) These three formulas are repeated in the above order between columns BK3 and FQ79 but I need to copy them down to 480. When I copy them one at a time it seems to copy fine but when I try to select multiple cells I get the following error messsage and Excel has to close. "Microsoft Excel for Windows has encountered a problem and needs to close. We are sorry for the inconvenience." Does anyone know of a fix or how to rewrite the formulas so it doesn't bomb out. Thanks for your help Joe |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel won't calculate my formulas correctly. | Excel Worksheet Functions | |||
Add-Ins for Excel 2003 - Financial Formulas does not download | Setting up and Configuration of Excel | |||
How to make Excel run limited number of formulas on a given worksh | Excel Discussion (Misc queries) | |||
How do I get to master Excel functions and formulas? | Excel Worksheet Functions | |||
Copying an Excel spreadsheet to a Word document | Excel Discussion (Misc queries) |