Forum Discussion
Excel formulas not working across multiple sheets
So I've got an excel document going where each month I am keeping track of data, then using auto sum for monthly totals. After that I do quarterly totals and then a year to date total. Each month, quarter, and YTD are their own sheet. In my sheets for Q1 through Q3, I reference the respective monthly sheets, then autosum the totals and it has worked flawlessly. For Q4, it pulled the monthly totals with no problem, but suddenly autosum does not want to function. I have automatic calculations turned on and the data is in there under general, so it's not a text issue. I can manually do it no problem, but I'd rather it be an automatic thing so I'm not editing multiple sheets each month. Please help. (there are no error messages either, it's just not adding the data)
9 Replies
You don't say what kind of data you're tracking, but it may not make a lot of difference. From a purely design point of view, you may benefit from a redesign of your basic method of storing data, a redesign that assembles ALL of the data into a single continuous database. We humans like to record data on monthly sheets, for clarity, ease of finding specific transactions. Excel, though, has marvelous tools to break things apart into monthly, quarterly, annual (semi-annual), (whatever) summaries. The Pivot Table for one can be very effective at summarizing years worth of personal (or work-related) income and outflow. There are other tools as well, but the Pivot Table is a good place to start.
I'm attaching a very simple example of that; I have my own data for nearly a whole decade stored on a single continuous table. Three credit card and three bank accounts.... all income, all expenses by budget category and sub-category. Without any formulas, Excel can produce a monthly cross-tabulated summary for any given year (or more, if desired). That's the power of the Pivot Table working with a single database.
In addition to the link above, if you are new to Pivot Tables you would benefit from the myriad videos on YouTube.
Could you attach a small sample workbook demonstrating the problem (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar?
HansVogelaar Each monthly total formula is correct because they're identical (with the exception of the actual month) to the other quarterly sheets.
- The status bar in the bottom left of your screen capture indicates a circular reference in E8. You may want to resolve that first.
