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

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

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