Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

I have a Google spreadsheet where each sheet is a student group, say, Group1, Group2, Group3 etc. Each sheet is organized identically.

I would like to summarize some information from all sheets into a final one. Since cell A1 of each sheet has the student group name, I'd like to code something like:

**Group name**  
=Group1!A1  
=Group2!A1  
=Group3!A1  
...

Is it possible to code/script this with Google Docs? I have the impression that yes it is, but I cannot find the way to code it (being pretty useless with spreadsheets, all apologies).

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

Update based on OP's feedback

What might help you then is =INDIRECT

For example: =INDIRECT("Sheet" & ROW() & "!A1")

What this formula does is uses the current sheet's own row to help define what information is being pulled. In this case, starting with cell A1, the formula will be evaluated as =INDIRECT("Sheet1!A1")

What =INDIRECT does is allows you to use text strings to define reference ranges when pulling data, thus you can use & to concatenate a string and insert an automated way of iterating through the various sheet numbers. To get the summarized data in A1 of all sheets, just drag the formula in the summary sheet all the way down the column to have the formula auto-fill each of the following roles. Here's a screenshot:

enter image description here

What's happening in my example is that on Sheet1 I have "Status" in cell A1. Looking at my formula in columnC, you'll notice that =INDIRECT is pulling that value from A1. The following entries is from autofilling (click and drag) the formula down to other rows, making =INDIRECT concatenate different Sheet #s based on the row().


OLD ANSWER Which does not use =INDIRECT

Without knowing more about the format of your specific Groups' sheets, you have it almost correct in your example.

If you rename sheets in your document to a custom name, you need to reference them using quotes. If you add single quotes around the sheet name, you've got it:

Group name  

='Group1'!A1  
='Group2'!A1  
='Group3'!A1  
...

You can also add summary information in a sheet, like:

Groups Total Sum

=sum('Group1'!B1:B13)

That will give you the sum of values in Sheet Group1, column B, rows 1 - 13.

Note: if you're ever confused as to how to reference a value from another sheet, you can always just:

  • Go to the summary sheet
  • Select the cell you'd like to edit
  • Hit = to start a formula and then
  • Click to another sheet/cell and see how the formula bar autofills the information.
share|improve this answer
    
I get how to cross-reference a value from one sheet to another. What I miss is a means to automatically insert the "Group1", "Group2", etc. references. If you prefer, imagine that I have a column holding the Group ID (1,2,3…) and that I need to insert a reference to "Group[ID]!A1" in the next column. I am trying to script the referencing of each group: I don't want to type Group1, Group2, etc. (I have many, maaany groups). –  Fr. Jan 8 '13 at 19:34
    
Ah ok, try the updated answer then :) –  OnenOnlyWalter Jan 8 '13 at 23:24
    
The updated answer is exactly what I needed (apologies for misleading you in the first wording). Your answer is clever and much more informative than the Google reference documentation on the matter. Thank you very much! –  Fr. Jan 9 '13 at 8:56
    
+1 helped me @Fr, thanks –  digger69 Apr 11 at 16:12
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.