Forum Discussion

George Weimann's avatar
George Weimann
Copper Contributor
Jan 07, 2018

Something off in my settings

I have 12 workbooks, each with 3 worksheets in them.  All with column headings as days of the year.  I am trying to create a master sheet totaling all of the columns in each workbook and each worksheet for each day of the year.  Simple enough, but for some reason, when I autosum each column to their respective workbook for January 1, then click and drag the formulas to each respective date, the formula for Jan 1 moves over and continues to calculate for Jan 1, instead of the worksheet advancing for each day of the year.  I'm sure its somewhere in the settings that isn't advancing the calculations for each day, but I cant figure it out.

excel
Formulas and Functions

1 Reply

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Jan 08, 2018
    When you copy a formula which points to another worksheet, the "link" to the sheet remains the same, only the cell addresses change. For this problem you might use the INDIRECT function.

    Suppose you want to sum cells A1:A10 on each sheet.
    Put the names of the worksheets on the summary sheet in -say- cells B2:B10
    Now in any cell on row 2 enter this formula:
    =SUM(INDIRECT("'"&B2&"'!A1:A10"))
    Drag the formula down.

Resources