How to Sum Same Cell from Multiple sheets in Google Spreadsheet

[ashish.d...@veolia.com]

Dear All,

I hope everyone will be fine and healthy. Actually from last couple of week I have been working on one formula like =SUM(Sheet1:Sheet7! A2) present in excel. I am trying develop the same formula in google sheet using app script. I have achieved the basic functionality of adding cell value but failed to archive dynamically.

the main goal to achieve is to sum the same cell in all sheets between two identified milestones. 

In excel, the formula is =sum(A:X!C1) for example.If I add a new sheet between these 2 milestones, data are automatically summed in my sheet. 

Kindly help me to achieve this goal.

thank you!! 

[Martin Molloy]

Try this

https://docs.google.com/spreadsheets/d/1VnCgNSErjyiss3Vu8kdpvLRmDU_096jr23FQxvRofGc/copy

It uses a custom formula. The main problem was trying to get it to recalculate when other cells change. Not fully resolved but I included a script from 

Tanaike <kanshi...@gmail.com>

which tries to address that. (reference is in the script)

Hope it helps

Martin

The information in this email and any associated files is confidential to Veolia Water Technologies (SASU) and/or any affiliate thereof and may be legally privileged. For the herein purposes, “affiliate” means any legal entity, partnership, joint venture, equity, company, including in particular any and all subsidiary which, directly or indirectly, controls Veolia Water Technologies (SASU) and/or is under the control thereof and/or is under the control of the ultimate parent company thereof, and “control” means the ability to directly or indirectly, direct the affairs of a third party by means of ownership, contract or otherwise. It may also contain information that is subject to copyright or constitutes a trade secret. It is intended solely for the named recipient. Access to this email by anyone else is unauthorized.

If you are not the intended recipient, please note that any use, disclosure, copying, distribution of this email or any action taken or omitted to be taken in reliance on its prohibited.

Warning: Although this email and any attachment thereto are believed to be free from viruses, it is the responsibility of the recipient to ensure that they are virus free. No responsibility whatsoever is accepted by Veolia for any loss or damage arising from their receipt or opening.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/5f631f3a-a87f-4ad8-ab4e-671ee777ba9fn%40googlegroups.com.

[ashish.d...@veolia.com]

Dear Martin,

Thank you very much for the solution. This is a kind of alternative we can try. Can you please help me to achieve this formula to make dynamic in term of cell selection. Because this formula we need to apply in around the 6000 cell and its respective cell ref. Means in Sheet 1, cell ref A1 contains all the sum of A1 cell from the between sheet . Here we are taking B5 cell reference but when we are trying to copy same formula in below cell its should take that cell reference and give the sum of the particular cell ref from between sheets.

Thank you!!