0

I have a (fully working) formula which looks across all the other sheets referred to in an array, and sums up all the costs in one of the columns in those sheets.

It works just fine - my question is further down, but let me give context.

The formula is:

=ArrayFormula(INDEX(
  QUERY(
    {
      '2017-01'!A$1:C$1000;
      '2017-02'!A$1:C$1000;
      '2017-03'!A$1:C$1000
    },
    CONCATENATE("select Col1, SUM(Col3) where Col1='", B2, "' group by Col1 label Col1 '', SUM(Col3) ''")
  ),
  0,  2
))

You can see the names of the sheets are simply year-month and the range is just the first three columns.

That formula is in entered into each of the "Total Cost" cells in a master sheet like this:

SKU     Code    Name    Total Cost
----------------------------------
0001    O8RE4   Things  3.93
0022    UO0V8   Stuff   28.39

And then each sheet called 2017-## has data like this:

Code    Name    Cost
--------------------
O8RE4   Things  9.68
UO0V8   Stuff   11.86

The formula spits out just a single number (no headers) which is the total of all the costs for each of the "Things" and "Stuff" items in all the other sheets.

Great! :)

But each month I (or someone less technical!) need to add another reference to the list of sheets, and then copy that down the column.

Question: Is there a way to reference a range of cells in another sheet (without using Google Script), which contains a list like this, which we simply add a new row to each month?

'2017-01'!A$1:C$1000
'2017-02'!A$1:C$1000
'2017-03'!A$1:C$1000
'2017-04'!A$1:C$1000
'2017-05'!A$1:C$1000
1

Without any custom function (Google Apps Script) the only thing you could do is have a sheet where you list them all out and reference them in that list. Basically manually make the list and have your formula reference the cells listing the sheet names.

If you want to go the route of a custom function (I see you are saying without, but I have to be complete...) see the thread Display Sheet Name in Google Spreadsheet. You will need to use the answers which tell you to create a custom function. It will require a modification to loop through to each sheet, though.

Explanation of doing the option in the first paragraph:

The INDIRECT() function Returns a cell reference specified by a string. So we can use that to combine a fixed range and the name of the sheet from a cell. Placing the Sheet names in row A, and this formula in B1, you will get the contents of the sheet named in cell A2, rows A and B from the first row to the last:

=INDIRECT(A2 & "!A1:B")

Making an array of the calls and applying this to a query(), this will return all items in the sheets listed in cells A1 and A2:

=query({ INDIRECT(A1 & "!A1:B"); INDIRECT(A2 & "!A1:B")}, "select * where Col2 <> ''")

The challenge is getting all the items in row A to create the array you are referencing. If you have the ability to write javascript, I would create a custom function. Since you said you could do a new file each year, you can add additional ; INDIRECT(A2 & "!A1:B") before the closing brace } and increment the A2 to A3, A4, etc...

I tried arrayformulas to get multiple INDIRECT() calls and a few other things but was not finding a good solution.

  • Thanks Karl but the answer you allude to in your first paragraph is what I am trying to do but failing. Can you please show an actual example of the syntax needed to refer to those other cells within the formula I gave? I am pretty good with Sheets but the syntax here is not clear. I'm not even sure it's possible at this stage. – RedYeti Mar 25 '17 at 10:34
  • Is there a limit to the number of sheets you may have? Going open ended is not working out with anything I am trying. – Karl_S Mar 27 '17 at 16:59
  • Well I guess it could be done yearly so there could be 12. Thanks for taking a look! – RedYeti Mar 28 '17 at 10:29
  • Just looking at your answer again, you mention using a custom function to get sheet names. I know you are just being complete there but since you mention getting the sheet names I want to be sure that you know for this question I am asking about adding each new sheet name manually to a dedicated sheet which only has the sheet names listed. Then referring to that list from the above function. I could build this in script easily (I'm a programmer) but I want to know if there is syntax available to do it they way I am asking :) – RedYeti Mar 28 '17 at 10:35
  • @RedYeti See my edit. – Karl_S Mar 28 '17 at 20:05

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

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