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.