1

I need to get sum of cell A1 of sheets from 1 to 5. It works fine with the direct formula which shows below:

=SUM('1:5'!A$1)

I also need to delete these sheets anytime and recreate them. As soon as I delete sheets 1:5, the formula results in #REF error.

To avoid this issue, I used indirect formula like below:

=SUM(INDIRECT("'1:5'!A1"))

However, it did not work and gave me #REF error. What I am doing wrong in above formula?

Below is a link to sample file if you need it: https://drive.google.com/open?id=0B7gzonuQsNbvS3R5MXRUX294cFU

Robin Mackenzie
  • 18,801
  • 7
  • 38
  • 56
TechGeek
  • 2,172
  • 15
  • 42
  • 69
  • 1
    Something like this may help you: http://stackoverflow.com/questions/4471884/how-to-turn-a-string-formula-into-a-real-formula. – Kirill Bulygin Feb 27 '17 at 07:18
  • 3
    `INDIRECT` won't accept a 3D sheet reference. Default solution is using sheets named "start" and "end" which are empty and does not be changed or deleted at all. Then a `=SUM(start:end!A1)` will sum all values `A1` of all sheets placed between the sheets named "start" and "end". – Axel Richter Feb 27 '17 at 07:24
  • Possibly useful: http://www.excelforum.com/excel-formulas-and-functions/921098-3d-sum-using-indirect-function.html – Robin Mackenzie Feb 27 '17 at 09:02
  • @KirillBulygin: This seems to be a good solution. Will it be good performance-wise too? – TechGeek Feb 27 '17 at 14:28
  • @Tejas Well, I think this depends on how a specific version of Excel handles such re-evaluations, there were different approaches on the referenced page. Another thing to try is to put your recreateble sheets into a separate file and see if the references to that file don't break when you recreate the sheets. – Kirill Bulygin Feb 27 '17 at 15:53

0 Answers0