0

I would like to create a summary sheet that reports the same data from across multiple tabs and doesn't rely on referencing individual sheet names in the formula/code. I have successfully done this when there is only one line of data representing each tab (using getSheetnames or Index scripts).

But, the data I want to summarize has multiple rows from each tab that need to be reported on a summary sheet.

I've successfully managed to combine and report the data from separate sheets into the Summary using a query/array combo where the query range includes each sheet and separated with ";". This can be done using cell references to the Sheetname list as well.

However, this needs to be more dynamic as sheets will be added/removed regularly and I would like to not have to re-write the query every time to add/remove individual sheet names.

SUMIF also works, but with similar limitations.

I believe my main challenge is using a list of sheet names which I can generate (listing one sheet per row) and associating those names formula/code that will produce summary results in multiple rows.

If Sheet names are in Column A, the following formula displays data combined from multiple tabs

=ArrayFormula(query({INDIRECT(A3&"!A2:A200"),to_text(INDIRECT(A3&"!B2:B200")),INDIRECT(A3&"!C2:C200");INDIRECT(A4&"!A2:A200"),to_text(INDIRECT(A4&"!B2:B200")),INDIRECT(A4&"!C2:C200")},"select Col1,Col2,Col3 where Col1 contains 'Project'"))

BUT, I want to reference my Sheetnames list without having to write in actual sheetnames or cell references to the formula.

Here's a link to a dummy workbook with sample.

player0
  • 124,011
  • 12
  • 67
  • 124

1 Answers1

0

I realize I'm late to the party on this question, but have you considered a Google Form with 5 questions:

Project? Phase? Transaction? Start? End?

That way, all your data would be in a single tab. People could input using the form, and you could still have project by project outputs for viewing/analysis using a simple query.

Google Sheets was designed from the ground up to be different from excel in specifically this way. There are all sorts of incentives to condense your data SOURCES while making it easy to disaggregate your ANALYSIS. While Excel is exactly the opposite. It's easy to keep track of things on tons of different tabs, and aggregate it all in one place. Yet there is no such thing as FILTER() or QUERY() to do the opposite.

MattKing
  • 7,373
  • 8
  • 13