Long formulae

[Guest]

Excel says that the formula I entered is too long...
I have many spreadsheets with various data and I need the formulae to fet
information from all my spreadsheets but it is too long...

I have made a list of all my spreadsheet tab names...
is it possible to have a short formula, with excel using this list of tab
names?

e.g. I have 3 spreadsheets 'A','B','C'. I need the sum of all A5 cells in
each of the spreadsheets. The conventional formula will be:
Sum(A!A5;b!A5;c!A5).

If there are many spreadsheets with longer names and a morecomplicated
function imagine how frustrating it can get!

Is it possible to use one formula with variable spreadsheet instead of
including the spreadsheet names in hte formula?

Thank you

 

[Guest]

Thank you very much
Next time I will ask a more precise question...Sorry!

 

[squenson via OfficeKB.com]

As far as I know the maximum length of a formula is 1024 characters. If you
need to fetch several cells from a separate spreadsheet which has a long path,
then you better split your formula into several cells:
- One cell in the current sheet for each cell you need to fetch;
- One cell for the formula, using the cell references you just created.

Not very elegant solution, but if anyone has something better to offer, feel
free to contribute!

Stephane Quenson.

 

[alasdair_gifford]

As far as I know the maximum length of a formula is 1024 characters. If you
need to fetch several cells from a separate spreadsheet which has a long path,
then you better split your formula into several cells:
- One cell in the current sheet for each cell you need to fetch;
- One cell for the formula, using the cell references you just created.

Not very elegant solution, but if anyone has something better to offer, feel
free to contribute!

Stephane Quenson.

You need to use a 3 dimensional reference, eg: =SUM('A:C'!A5), note
the ' before and after A and C, this denotes the 3D reference. Works
as long as all sheets are in one wokbook and are all in cell A5 on
every sheet.

Giff

 

[Harlan Grove]

You need to use a 3 dimensional reference, eg: =SUM('A:C'!A5), note
the ' before and after A and C, this denotes the 3D reference. . . .

No, the colon ) between tokens to the left of the exclamation point (!)
denotes the 3D reference. The single quotes are a good idea all the time,
but they're only needed when worksheet names contain spaces.