Long formulae
 

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

Long formulae
 

=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