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:
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.
If you download the old Doc as an XLSX, then upload and convert to Google doc version, it upgrades the doc. I haven't found anything it doesn't keep yet. It even kept my named ranges.
[EDIT] I did have to "tickle" some functions by removing, then re-adding the = sign in front. Or double-clicking the cell, then just hitting enter. Maybe I just wasn't patient, but the majority of my complex spreadsheet was still functioning after the procedure above.
Best Answer
I've searched the net and wasn't able to find a solution for you. As per this answer on SO, it's also not possible to do it via Google Apps Script.
Copying from a template, having no gridlines, is just as quick as deselecting the gridlines.