Can I change just the sheet, but keep the cells in multiple references

Copper Contributor

I have a workbook that tracks data in monthly tabs.  I also have a sheet that pulls multiple datapoints from each worksheet for an easily printable report.


Since each of the monthly sheets is identical, is there a way that, as I go into a new month, I can change just which sheet my report pulls from, but keeps the cell references?


For example if I have data pulling from =May!A1 , =May!C3,  =May!F45, is there an easy way to change all of the Mays to Junes?


Thanks in advance.

3 Replies


I can think of two possibilities here. The most obvious and simplest is to use the search and replace dialogue.

Search '=May!'

Replace '=June!'

Search in formulas must be switched on.

Or you can rebuild your formulas and use the INDIRECT() function=



Then you get =June!A1 if cell Y1 contains the word 'June'.


No guarantees, but it may be possible to use 3D ranges.  For example, if cell A1 contains the sheet name




= TOCOL(sheetName)

= TOCOL(revenue)
= SUM(revenue)

would be a list of the months taken from the sheet names, the revenue for each month and the total revenue respectively.  And not a direct cell reference to be seen!

@dscheikey The Search and Replace was it!  Super simple.  Thank you so much!