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?

  • Welcome. Your explanation of the layout of your spreadsheet and the sum formulas is difficult to follow and visualise. Would you please edit your question to provide an example of the expenses sheet as well as a monthly sheet containing some data, and the relevant sum formula. Would you please also summarise your research and any work that you have done to solve your problem.
    – Tedinoz
    Jul 18 '21 at 3:08

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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.