Forum Discussion

morganjr23's avatar
morganjr23
Copper Contributor
Jun 09, 2022

Formula sheet increment

I have a sheet that takes the total sum from other sheets cell U48. My current formula is =‘001’!U$48.  I have to create a totaling sheet that will reference U48 from sheet 001-100. How do I create a formula where the sheet number is dynamic and will increment up when I drag my formula down.

3 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    If you really need to pull in each individual sheet total then you can use INDIRECT():
    =INDIRECT("00"&ROW(A1)&"!U$48")
    and then drag down
  • mtarler's avatar
    mtarler
    Silver Contributor
    In special cases you can use 3d references which basically allow you to sum across sheets:
    =SUM('001:100'!U$48)

Resources