Forum Discussion

rkg4742's avatar
rkg4742
Copper Contributor
May 17, 2024

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?

 

Thanks in advance.

Excel
Excel on Mac
  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    May 17, 2024

    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's avatar
    dscheikey
    Bronze Contributor
    May 17, 2024

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

Resources