How to duplicate the formula that sum across multiple sheets

Atipol Kanchanapiboon

We set up the sheet to report the daily sale volume of each project in the department. Each sheet is for one department. We create duplicate sheets for all departments in our company. Please see the screenshot.

Now, we create a similar sheet to calculate the sum of the sale for each project for each day across all departments. For example, Product1 on March 1st is the sum of the volume from department A, B, C, D, E, F, G, H, ... This works fine for us.

The question is how do we duplicate this formula for Product2, 3, 4, 5.. and also for March 2nd, 3rd? I try to copy and paste and the formula did not update to pick up the new cell from each sheet. Manually creating the formula for each cell is not an option since we have many products and departments.

Thank you.

Genevieve P.

Hi @Atipol Kanchanapiboon

Could you clarify what the end-use is for this data? For example, are you just looking to see the number or are you wanting to create a chart on a Dashboard?

The fastest/easiest way to gather this data would be to create a Report (see here). You would want to select all your different Department Sheets as the source sheets for the Report and combine all the data together.

Then you can filter the Report by Date (either by month or by a specific date). Finally, you can then use the Summary feature (see here) to automatically SUM together each of your columns and present that data at the top of the Report, like so:

Would that work for you?

Cheers!

Genevieve