Formula Size Limit

[jmeeko]

Is there a way to overcome the the formula size limit of 1024. MS indicates about 32k is the permissible character length, but apparently formulas are treated differently (even though I could not find this documented in MSDN or TechNet). No, my users will not accept the result of the formula instead.

My code simply creates an array of row numbers needing to be totaled at the end, so it's just a matter of simple addition of cell references (e.g. "=A1+A13+A25..."). Any suggestions on a workaround??

Thanks all.
This message was edited by jmeeko on 2002-12-10 14:53

 

[Aladin Akyurek]

On 2002-12-10 14:51, jmeeko wrote:
Is there a way to overcome the the formula size limit of 1024. MS indicates about 32k is the permissible character length, but apparently formulas are treated differently (even though I could not find this documented in MSDN or TechNet). No, my users will not accept the result of the formula instead.

My code simply creates an array of row numbers needing to be totaled at the end, so it's just a matter of simple addition of cell references (e.g. "=A1+A13+A25..."). Any suggestions on a workaround??

Thanks all.
This message was edited by jmeeko on 2002-12-10 14:53

Given

=A1+A13+A25...

you appear to sum every 12th cell in a vertical range in A. So try...

=SUMPRODUCT((MOD(ROW(A1:A600)-CELL("Row",A1:A600)+0,12)=0)*(A1:A600))

 

[jmeeko]

Sorry for any pattern in my example, but there isn't one in reality.

During the sheet's creation process I am pulling records from a database so there is no way to anticipate any pattern in how many detail records there will be for any given group.

Thanks.

 

[Joe Was]

In one cell say b1 put the bulk of your formula, +a1+a5... then in another cell say b2 put your first formula cell address and the rest of your formula, b1+a12+a22...

This way you can concatenate formulas across more than one cell and get around the formula size limit. Hope this helps. JSW

 

[jmeeko]

I had considered this is a possibility, although I'm not looking forward to the implementation due to the complexity and size of the spreadsheet already.

Sounds like it may be only only solution short of asking the user to decrease the size of the imported dataset or to use fewer subtotal groupings.

Thanks.