0

I used this post to have all Google Sheets IDs in a folder Getting all files' file-id from a folder in Google Drive

I would like to make a formula or script to sum values in table across all these sheets IDs (note, the table is present in all sheets with same cell range).

Instead of copying and pasting the sheets Ids manually every time, I would like to make formula with variables that represents sheets ID so that every time I just past the sheets ID and the variables are assigned for each sheets.

Some thing in my mind like this:

a=1bsKy3bx9x2sYbM4BBvUjaYDdtveL1XV1l8wp8-CUD1c

b= 1fUb8H44PpeZpu40mfzK1_vuNEoLAyhiHjNcSOGTfLJU

c=1CckyrVFklIdQZa6PCM6mByvV9UK14zEwnoaK0PueTHg

And so on... while the formula is fixed

5
  • Please show what you tried. – Rubén Jan 9 at 22:15
  • 1
    Are you familiar with/have you experimented with importrange? – Tedinoz Jan 9 at 23:22
  • 1
    Does this answer your question? How do I link a cell in Google Spreadsheets to a cell in another document? – Tedinoz Jan 9 at 23:26
  • '''=SUM(IMPORTRANGE("1CckyrVFklIdQZa6PCM6mByvV9UK14zEwnoaK0PueTHg", "'statistics'!d6"), IMPORTRANGE("1bsKy3bx9x2sYbM4BBvUjaYDdtveL1XV1l8wp8-CUD1c", "'statistics'!d6")) ''' But i need to copy paste this formula in every cell to count and change the cell range as well.. Also I need to copy the sheet ID every time I need to which is a long process>> my project is having a specific sheet ID for every day in a month.. so monthly I'm counting /sum all 30 sheets Ids in this way.. so i have to repeat for every month.. definitely there is a way but I'm still looking for – Hassan Almaateeq Jan 10 at 3:14
2

You can do the following set-up

In cell C2 place ="'statistics'!d6" and so on

Then in cell D2 the below formula and drag it down

=SUM(IMPORTRANGE($B$2, C2),IMPORTRANGE($B$4, C2))

enter image description here

1
  • Thats awsome... Thanks.. you save my life> I could add lot of ranges and sheets ID – Hassan Almaateeq Jan 10 at 11:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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