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

Copper Contributor

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

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?

3 Replies

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

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'.

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

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!

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

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