Need to sum cells from multiple spreadsheets into one total spreadsheet. | rbeitgal | 3/13/09 3:09 PM | Can you please help me.
I have multiple spreadsheets with multiple worksheets. I need to add each cell in a worksheet in a specific row and column to a grand total spreadsheet with a matching worksheet. I don't want to go into each cell and create an individual formula to add them up across worksheets. That would take forever. Is there a way to add cells from matching worksheets into one grand total. Note: there might be words in some of the cells. ex. total
Thanks in advance for your assistance.
ex.
spreadsheet 1, worksheet 1, row 1, column a =12; row 2 column a = 5
"" "" row 1, column b =10; row 2 coumn b = 3
spreadsheet 2, worksheet 1, row 1, column a = 9; row 2 column a = 8
"" "" row 1, column b = 6; row 2 coumn b = 1
want to add spreadsheet 1 and 2 into spreadsheet 3
spreadsheet 3, worksheet 1 row 1, column a =(12+9) =21; row 2 column a = (5+8)= 13
"" "" row 1, column b =(10+6) =16; row 2 coumn b = (3+1)=4
_________________________________________________________________
spreadsheet 1, worksheet 2, row 1, column a =11; row 2 column a = 4
"" "" row 1, column b =9; row 2 coumn b = 2
spreadsheet 2, worksheet 2, row 1, column a = 8; row 2 column a = 7
"" "" row 1, column b = 5; row 2 coumn b = 0
want to add spreadsheet 1 and 2 into spreadsheet 3
spreadsheet 3, worksheet 2 row 1, column a =(11+8) =19; row 2 column a = (4+7)= 11
"" "" row 1, column b =(9+5) =14; row 2 coumn b = (2+0)=2 thanks 2009-03-16 I must be doing something wrong because excel keeps showing this as an invalid formula.
"key_spreadsheet1" = assumed this the name of the first spreadsheet I want to grab the information from. ex. spreadsheet 1
"worksheet1"= assumed this is worksheet name in spreadsheet 1. ex worksheet 1
Is the problem I have spaces in the name?
Is the problem that I used the double quotes and these are not suppose to be in the formula at all.
Are the spaces between the parenthesis important?
Are the quotes around "worksheet1!A1:b2" suppose to be around the range too?
thanks for your help
2009-03-17 Nope, I am using Excel. Is there anyway to do this in Excel. |

Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. | ahab | 3/13/09 4:20 PM | Try: In spreadsheet3 , sheet 'worksheet 1' cell A1:=ARRAYFORMULA( N(ImportRange( "key_spreadsheet1" ; "worksheet1!A1:B2" )) + N(ImportRange( "key_spreadsheet2" ; "worksheet1!A1:B2" )) ) In spreadsheet3 , sheet 'worksheet 2' cell A1: =ARRAYFORMULA( N(ImportRange( "key_spreadsheet1" ; "worksheet2!A1:B2" )) + N(ImportRange( "key_spreadsheet2" ; "worksheet2!A1:B2" )) ) |

Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. | ahab | 3/16/09 4:14 PM | I presume that by excel you mean Google Docs spreadsheet, as Microsoft Excel does not support the used functions. "key_spreadsheet1" is the key value in the URL of the spreadsheet, not its name. "worksheet1!A1:B2" is the sheet name and the range as you would have them in the spreadsheet, i.e. a sheet named worksheet1 and the range A1:B1. Both parameters need to be inbetween double quotes. E.g. to reference cell A1 in sheet 'Start' in the spreadsheet "Copy of example get latest submit", with the URL https://spreadsheets.google.com/ccc?key=pPbcejygf5gbvQFKr3vtrQg&hl=en using an ImportRange function would be done like this: =ImportRange( "pPbcejygf5gbvQFKr3vtrQg" ; "Start!A1" ) If the sheet name contains spaces these should also be used in the second parameter, however any single quotes should not be used. E.g. a sheet reference like 'Sheet number one'!A1 would be specified as "Sheet number one!A1" |

Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. | ahab | 3/17/09 1:04 PM | This forum is for Google Docs questions. And I already stated the formulas used are not supported by Microsoft Excel. You better ask again in a forum with Excel specialists. |

Re: Need to sum cells from multiple spreadsheets into one total spreadsheet. | K2HUNTERR | 1/5/10 10:10 PM | I have a question about this. Say for example I have spreadsheets that are in sequence of dates i.e 1/2/10, 1/3/10, 1/4/10
and i want to tally cell A100 on sheet 1 of all three into one. How do i go about doing this with out supplying the key each time a new spreadsheet is created i.e tommorrow and the next and so on...
Kiel |