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.

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.

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.

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.

.