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

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

"Toppers" wrote:


=SUMPRODUCT(SUMIF(INDIRECT("'"&$H$1:$H$3&"'!A1:A10 0"),"=John",INDIRECT("'"&$H$1:$H$3&"'!B1:B100") ))

Change "=John" to whatever you want to compare (could be of form "=" & X1
where X1 is value to be compared)

Cells H1 to H3 contain names of your worksheets: extend as needed

And obviously change ranges A & B as required.


And ask the right question if you want a "right" answer!

"Yiannos" wrote:

The function I want to use is the "sumif" and i don't think it works this way.

Is there another way?

"Toppers" wrote:

Create tow shetts named "Start" and "End" and place shhets to be summed
between these then:

=SUM('Start:End'!A5) will sum A5 in all sheets between Start & End

HTH

"Yiannos" wrote:

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

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.

Yiannos wrote:
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

--
Message posted via http://www.officekb.com

On Aug 19, 12:42 pm, "squenson via OfficeKB.com" <u36146@uwe wrote:
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.





Yiannos wrote:
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

--
Message posted viahttp://www.officekb.com- Hide quoted text -

- Show quoted text -

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

wrote...
....
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.