Active Member
August 18, 2017
Hi....
My workmate came cross this formula below in an excel file which she was given, due to the data security issue she can't share the file. I am just wondering if anyone knew about this command and how it works.
=SUM('>>:<<'!S46)
Many thanks in advance for your help....
Cheers!
Jessica
VIP
Trusted Members
June 25, 2016
Active Member
August 18, 2017
Hi Mynda and Sunny,
Thanks for your prompted reply, really appreciated. I finally saw the actual file and worked it out.
It was a very smart way of summing up the same cell from a group of sheets.
E.g if I want to sum up cell A1 in sheet 1, 2, 3, 4 & 5, I would normally use formula =SUM(Sheet1:Sheet5!A1), but if I need to add more sheet(s) to the group, I have to either update sheet name for the last tab in the formula or make sure the added sheets are in between Sheet 1 & Sheet 5.
This smart person added a blank sheet called ">>" at beginning of the group sheets and another blank sheet called "<<" at the end. So the formula became =SUM('>>:<<'!A1), so you can add any no. of sheets to the group in any order without the need to update the formula.
We learn something new everyday! 🙂
Thanks again for your help and have an awesome day!
Cheers!
Jessica
Answers Post
VIP
Trusted Members
June 25, 2016
Hi Jessica
When doing 3D summing, make sure that the new sheet(s) are added between the first sheet ">>" and last sheet "<<" else it won't be included in the sum.
Example. If you add a new sheet before the sheet ">>" or after the sheet named "<<", it will not be summed.
Sunny
1 Guest(s)