Update range in formula easily
Posted on 2013-02-05
I have an excel workbook that has 2 worksheets in it. the first one is a summary report the other is the data page that the summary is built from. on the summary report sheet I have hundreds of cells that have formulas in them. each quarter i get the new data for the data sheet since there is maybe more lines per section of less lines I then have to go to the summary report and change each one of the formulas to reflect the correct ranges. all the other parts of the formula stays the same. for example. last quarter the formula in 1 cell looked like this:
=SUMIFS('Business Objects_Data'!P188:P211,'Business Objects_Data'!R188:R211,"PW0",'Business Objects_Data'!F188:F211,1)/E2
and this quarter it looks like:
=SUMIFS('Business Objects_Data'!P189:P212,'Business Objects_Data'!R189:R212,"PW0",'Business Objects_Data'!F189:F212,1)/E2
see the only thing that changed was the data ranges. is there a way to automatically do that?