How do I copy cells so that only the sheets where they reference from is changed?

Question

I have a expenses sheet, and other sheets named Jan, Feb, etc. These sheets are identical except for their numerical values.

In the expenses sheet I want to have columns, each of them applying to a separate month, and I want these columns' cells to reference information inside the relevant month's sheet.

For example, for January, I will have cells of the form =SUM('Jan'!$D2:$Z2). I of course have many such cells (each referring to a different row in Jan). How can I generalize this so that I'll have this behavior for the other months? That is, so that the relevant cells will be of the forms =SUM('Feb'!$D2:$Z2), =SUM('Mar'!$D2:$Z2) etc.

Or, to summarize the question: How can I copy multiple cells that reference another sheets so that only the sheet to which they are referenced is changed?

Answer 1

You can put the names of the months/sheets in a column, let's say "jan" is in A2, you can use a formula in B2 that uses the value in A2, indirect and concatenate to find the sum of one sheet:

=SUM(indirect(concatenate(A2, "!$D2:$Z2")))

Now you can put feb, mar etcetera in the columns below, and drag/copy the formula to get the results for the other months too.

Try it out in this little demo i prepared for you.