Summing in one summary tab data from multiple tabs

Hi, and welcome to the forum.

You are making the same mistake that I see time and time again. You are mixing up the two elements of data capture and final reporting. The two require quite different treatments.

A lot of people start by designing the form that they expect to see as the final report, and then wonder why it's so difficult to subsequently analyse and summarise or extract information from it, Yours exhibits all those features.

You should always capture data in a simple two dimensional table and worry about reporting information from it afterwards. Without exception doing this you will always be able to easily obtain management information. Rarely is this the case if you start the other way round.
You will also throw open the whole wonderful world of the powerful Pivot table functionality.

Ditch the separate consultant tabs and have a single tab in which you have four columns for

Date
Project Ref
Consultant
Amount

and record all data in this table as it arises. You can simplify data entry by having drop down validation cells for consultant and project. Apply a dynamic range name to this table so that the name always adjusts as new data is added and use this name as the source of the data for a Pivot table.

Originally Posted by loulite

Hi Richard,

Pivot tables would be perfect, unfortunately the tabs are populated by different project managers that do not see each other's tabs. That is why I have to use multiple tabs.

Why?

If you are having separate workbooks for each consultant and then bringing them all together as many tabs in the master workbook your original post and example should have mentioned that rather important point otherwise many of us waste our time answering something when we don't have all the information.

Irrespective of that if you don't want consultants to see each others data then just separate the database tab (and presumably the pivot table tab) from the tab which only holds the data input row and make the database tab VeryHidden so that they can't unhide it from within the Excel App. So for the consultants they only ever see the single input sheet.

Even if you want to maintain separate workbooks for each consultant, don't bring them all together in many tabs in the master workbook, stack them one underneath each other on a single tab then you can use a pivot table.