If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Exceeding 1024 limit on formula length
I'm supporting an application using Excel 97/VBA that
needs to consolidate totals from corresponding cells in many different workbooks into another workbook. The technique being used in code is to cycle through the workbooks and build the formula concatenating to the previous formula each time. This works well except that the file names and sheet names are quite long (to make them descriptive for users) and in some cases the formula can exceed the 1024 limit by a factor of 2 or 3. Is there another way to approach this that is, if not unlimited, less expensive in use of formula characters? Unfortunately I can't just accumulate totals or use the Consolidate function because of the user requirements. It is an option to upgrade the users to Excel 2002 or 2003 if necessary. |
#2
|
|||
|
|||
Exceeding 1024 limit on formula length
Hi
you may try to define a name for some parts of your formula (e.g. for fixed calculations, etc.). -- Regards Frank Kabel Frankfurt, Germany "Geoff Goodacre" schrieb im Newsbeitrag ... I'm supporting an application using Excel 97/VBA that needs to consolidate totals from corresponding cells in many different workbooks into another workbook. The technique being used in code is to cycle through the workbooks and build the formula concatenating to the previous formula each time. This works well except that the file names and sheet names are quite long (to make them descriptive for users) and in some cases the formula can exceed the 1024 limit by a factor of 2 or 3. Is there another way to approach this that is, if not unlimited, less expensive in use of formula characters? Unfortunately I can't just accumulate totals or use the Consolidate function because of the user requirements. It is an option to upgrade the users to Excel 2002 or 2003 if necessary. |
#3
|
|||
|
|||
Exceeding 1024 limit on formula length
See one answer in .worksheet.functions
In article , "Geoff Goodacre" wrote: I'm supporting an application using Excel 97/VBA that needs to consolidate totals from corresponding cells in many different workbooks into another workbook. The technique being used in code is to cycle through the workbooks and build the formula concatenating to the previous formula each time. This works well except that the file names and sheet names are quite long (to make them descriptive for users) and in some cases the formula can exceed the 1024 limit by a factor of 2 or 3. Is there another way to approach this that is, if not unlimited, less expensive in use of formula characters? Unfortunately I can't just accumulate totals or use the Consolidate function because of the user requirements. It is an option to upgrade the users to Excel 2002 or 2003 if necessary. |
#4
|
|||
|
|||
Exceeding 1024 limit on formula length
Many thanks - this will solve our problem.
-----Original Message----- Hi you may try to define a name for some parts of your formula (e.g. for fixed calculations, etc.). -- Regards Frank Kabel Frankfurt, Germany "Geoff Goodacre" schrieb im Newsbeitrag ... I'm supporting an application using Excel 97/VBA that needs to consolidate totals from corresponding cells in many different workbooks into another workbook. The technique being used in code is to cycle through the workbooks and build the formula concatenating to the previous formula each time. This works well except that the file names and sheet names are quite long (to make them descriptive for users) and in some cases the formula can exceed the 1024 limit by a factor of 2 or 3. Is there another way to approach this that is, if not unlimited, less expensive in use of formula characters? Unfortunately I can't just accumulate totals or use the Consolidate function because of the user requirements. It is an option to upgrade the users to Excel 2002 or 2003 if necessary. . |
Thread Tools | |
Display Modes | |
|
|