google sheetsimportimportrangepivot table
I'd like to use another spreadsheet's data as the data range for a pivot table.
In order to import data from another spreadsheet, one can use the impotrange function.
But can it be imported directly into a pivot table?
Such an attempt states it's not a valid range:
Excel allows a Grouping facility for what you require but I see no equivalent in Google Docs. A workaround is to add flags in your source data. I did so in Columns B and C with:
=MONTH(A2) and =WEEKNUM(A2) respectively.
then included these as rows in the pivot.
In the image months 1 and 3 (I changed the dates from those in your example) have been rolled-up but not month 4:
Strictly speaking, you shouldn't have it both ways: maintain a link between two data sets, yet edit one of them independently of the other. If one needs to exist independently of the other, then perhaps you should just copy-paste the values.
But here is a workaround which might suffice in your case.
importrange command on another sheet, say Sheet2.=Sheet2!A1 in cell A1, and copy this formula to the entire sheet (Ctrl-C, Ctrl-A, Ctrl-V)
Best Answer
It's not possible to import range by adding a formula in the Pivot Table editor, you have to add there a reference to a range in the current spreadsheet.
If you don't want that the sheet with the imported data be visible you could hide it.
Another alternative is to use Google Data Studio. Depending on the purpose of your pivot table, you could use GDS to "explore" a data connection to the source spreadsheet or to create a report.