Forum Discussion
Consolidating tabs to a summary worksheet
I am trying to consolidate data from multiple tabs to a summary worksheet. It is a caseload report that is broken out by case manager on each tab. The headings are the same on each tab. I would like a summary tab that will consolidate all the case managers clients in one place for the supervisor and will update as the case managers update their tabs. I am using the consolidate function in the data menu, however when I try to consolidate, I am getting an error that states the following:
This won't work because it would move cells in a table on your worksheet.
I am not sure what I am doing wrong. Any suggestions would be appreciated.
Thank you
- mathetesSilver Contributor
This isn't what you're asking for, not directly at least, and may not be practical in your situation, depending on how those separate tabs are maintained.
In general, though, it is a LOT easier to produce a consolidated report from a single database, and you have the makings of a single database already (you mention that all the headings are the same on each tab). All you'd need to do is add a column to a central database that identifies which case manager the row applies to.
From that single database, then, it would be extraordinarily easy for the supervisor to get an up-to-date report on the current status of caseloads, etc., etc.
But that presumes that the data on each tab is entered by the same person OR that there's not a privacy issue or policy that mandates separate tabs and "for your eyes only" viewing privileges of the individual reports.
That aside, if you for whatever reason need to keep things as they are, you might want to look into Power Query--or somebody else here might be able to help with that. I haven't personally worked with Power Query. But to the extent there's flexibility in the design of your process when it comes to storing (and then retrieving) the raw data, I'd be happy to explain more my thoughts above.
- Josey_McDonaldCopper Contributor
Thank your for the reply, I am not sure I am following what you are saying. One of the columns on each tab is already the case manager's name. I want the data from each tab to then be consolidated onto a summary tab that shows all of the case managers clients and data etc. in one place for the supervisor. I would like the summary tab to update in real time when the case manager updates his/her tab. Could you elaborate on your comment?
Thank you!
- mathetesSilver Contributor
Let's back up just a bit. It's not uncommon for people who are beginning with Excel, seeking to create a system to track various kinds of activities, to bring to the task the mindset that made a lot of sense back in the days of paper record keeping. So, for example, we'd track expenses on a monthly basis in a ledger sheet, and keep one sheet per month; many a first effort at tracking expenses in Excel begins with that same design.
Or follow sales of products in a clothing store, with separate sheets for separate categories of types of clothing. Or separate sheets for which clerk was serving the customer.
Or which case manager was handling which case.
Nothing wrong with that. It especially made sense on paper. It sometimes continues to make sense in the computer. But that arrangement actually, though "clearer" to our human way of thinking, gets in the way of consolidating and producing higher level summary reports when it comes to taking good advantage of what the computer--using Excel in this case--can do so easily.
A single database (putting all that data in any of the foregoing examples and putting it into one sheet) can still be the source for individual reports on expenses by month, sales by category, sales by clerk, caseloads by case manager. AND that single database can easily be the source for higher level summary reports, slicing and dicing the data in any number of ways quite easily.
The fact that you already have the case manager's name on each row of each individual sheet (where, by the way, it's redundant; unnecessary since the whole tab refers to that individual case manager) means it would be easy to put them all together.
So then the question is in what manner do you want the data consolidated? Numbers of cases? Amount of time each case manager has spent on each case? Average cases per week, per month? etc. What you're asking about is often called a "Dashboard"--a sheet that pulls relevant data from the database. There are lots of resources on this topic. Here's an introductory one from Microsoft. You'll also find many YouTube videos on the subject.
Summarizing all of this rambling on my part:
- Using a single database is generally the easier way to organize your source data--unless there's some practical or legal or policy reason to the contrary--easier than keeping that "raw data" on a separate tab for each case manager.
- There are many ways to create a consolidated report, many questions a consolidated report could be answering for the supervisor: what are the questions you want to answer in this case?