Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Clearing cells takes long, long time | Excel Discussion (Misc queries) | |||
Long formulae | Excel Worksheet Functions | |||
Long Date - Long Date = text is days | Excel Discussion (Misc queries) | |||
Searching TEXT in formulae, rather than results of formulae | Excel Worksheet Functions | |||
Help with formulae | Excel Worksheet Functions |