summing across multiple worksheets with automatic updating

**hughesb6** · 12-30-2014, 05:24 PM

I have a small workbook with 3 sheets in it. The sheets are named Jan. Feb. & Annual. (I will be adding the other months of the year when I get it working correctly.)

In the sheets Jan & Feb, I have one column labeled "Expense Category", and one labeled "Amount". In the Annual sheet, I have used the consolidate command. The Category and the Amount show up and are adding together correctly. My problem is that when I add a new expense, it does not show on the Annual sheet. New data is not getting updated in the Annual sheet. Is there a simple solution? I have automatic update turned on. Any help is appreciated greatly.

**teylyn** · 12-30-2014, 05:28 PM

Hello,

the simplest solution is to stick with one of the golden rules of good spreadsheet design. Data entry in one sheet. Reporting on others.

The problem is created by splitting the data entry into different sheets. If you use just one sheet, you can then have a report that can be filtered for each month or shows the data for the whole period. Two sheets total.

It is always easier to work with good data architecture than to write workarounds and complicated formulas for bad architecture.

cheers, teylyn

**hughesb6** · 12-30-2014, 05:59 PM

Teylyn,

Thanks for the suggestion. Putting 12 months of expense data in a single sheet is going to make the sheet too large. Any other thoughts?

**teylyn** · 12-30-2014, 10:36 PM

What is "too large"? If your Excel version is 2002, then you still have more than 64k rows of data.

Spreading data over several sheets and then trying to report on all data in all sheets is never easy. You are seeing one of the problems of that approach.