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

@rkg4742 

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=

 

=INDIRECT(Y1&"!A1")

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

@rkg4742 

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

sheetName
=Apr:Jun!$A$1

revenue
=Apr:Jun!$F$45

then

= 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!