How to copy paste a SUM function that is referencing to other sheets

AnneBolsius

Hi all,

I have sales data per month for a year, for several regions, each region has its own sheet. When I want to make 1 summary table, I think I need to use =SUM(number 1, number 2, ...and so on) and for each number, reference to another regional sheet. In Excel, if I create this formal for January once, I can then drag the cell to copy the formula for all other months. When I do that in Smartsheet, it will keep the formula for January intact. How can I easily create a formula for 1 month and then copy it over to other months? I want to prevent to be typing the same formula 12 times, since I need to reference quite a number of sheets each time.

Thanks for your help again!

Kind regards

Anne

Paul Newcome

Are you able to provide screenshots that show how the source data is laid out as well as how your metrics sheet is laid out?

AnneBolsius

Hi Paul,

Please find attached. I have 1 summary sheet, that needs to summarise data from 3 underlying sheets (Portugal, Spain, France). When I enter the formula in January, I would expect to be able to copy it to other months. Now I only know to solve this by manually entering and referencing the sheets, but I have more countries, so it becomes way too much work.

.

Kind regards

Anne

Paul Newcome

How is the source data laid out?

AnneBolsius

Hi Paul,

In separate sheets, but in the exact same table. Each country is on another sheet.

Paul Newcome

Ok. To be able to dragfill a formula, you will need to use an INDEX/MATCH to pull the value over based on the month. Then replicate the INDEX/MATCH for each sheet. Then next each one into the SUM function.

My personal recommendation would be to create a separate column on your metrics sheet for each country and use cell linking to pull the values over. If you highlight all 12 cells in the column on the metrics sheet and then go through like you are creating a cell reference, you can select all 12 cells on the source sheet and link them all at the same time.

Then you can insert a final column that will total across the row for all countries. Now when you add a new country, you insert a new column on the metrics sheet, create all 12 new cell links a the same time, and make sure that the sum formula in the final column includes the new country column.

AnneBolsius

Thanks Paul, the INDEX/MATCH solution works for me. The formula is quite long and complicated, any idea why I can't just sum up cells from other sheets?

Paul Newcome

When you create a cross sheet reference, the range is static so that it won't adjust when you dragfill.