I have a workbook with multiple worksheets and I want get data from each worksheet total it and place the result into another worksheet within that workbook.

Each sheet is an individual product, listed on each sheet is the ingredients that make up the product. Column headings Autumn, Winter, Spring, Summer and row heading for each ingredient used. For example Pleurisy Root (herb) is used in a number of different products and I want to add up all the Pleurisy Root used in Autumn, and put the result in a master sheet that displays the total amount of Pleurisy Root used in Autumn.

As I have limited experience with Excel I had been using the following formula



'Sheet_1'!E29+'Sheet_2'!E29+'Sheet_3'!E29

In the workbook there are 98 product sheets and Pleurisy Root is used in 45 of the products so the above formula ends up being incredibly long, time consuming to create and prone to mistakes. Is there a solution that will lookup the Pleurisy Root used in Autumn on each sheet, total it and place the result in the master ingredient sheet.

Hope this is enough info and is understandable, thanking you in advance for your help.

Safrac

If you need to sum the alue from the same cell on each sheet have a look at 3D-referencing

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi Pecoflyer

That worked a treat, your a legend, thank you for your advice.

Safrac

Oh ! Being 64 makes me a legend ?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

In my case, the 3D-reference link sent me to microsoft ad for Office 365. However, a simple browser search for "3D reference" did send me to a useful MS posting: Create a 3-D reference to the same cell range on multiple worksheets

64 made me old. I'm now 70 and OLDER !

The thread being 8 years old ( which makes me 72), no wonder links can point to new pages

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

And whenever a new project is added, you have to redesign your workbook - again and again!

Design your workbook more database-style instead! E.g.:

You have a sheet where all projects are registered (E.eg. in defined Table tProjects: Project, [ProjectDate]/[ProjectYear]/ProjectStartPeriod]/[ProjectEndPeriod] - you have to decide how you indicate the period the project applies to. In case you want the workbook have the data of several years, the periods must have year included - like 202201 for January of current year. In case you start a new workbook every year, you can omit the year number). You have to define the Project column of Projects table as dynamic Named Range, to use it in Data Validation lists in other worksheets (e.g. lProjects);

You have a sheet where all components used in any project are registered (E.g. in Table tComponents: Component). The Column Component must be defined as Dynamic Named range (e.g. lComponents)

You have a sheet where all project components are registered (E.g. in Table tProjComp: Project, Component, Period, Amount). Project column is defined as Data Validation list, based on lProjects, so only registered projects can be added. Component column is also defined as Data Validation List, based on lComponents. In case periods you will use are always only four ones ("Autumn", "Winter", "Spring" and "Summer"), you can define data validation list for Period column directly. In case periods must be determined e.g. for specific year, you need a separate worksheet with Table (tPeriods: Period) where those periods are defined manually (in format like "2022-Summer" or 202201, or whatever), or are calculated, and where a dynamic named range containing defined periods is created, to be used as source for Data Validation list;

You can create a report sheet, where you select a project, and all component info from tProjComp for this project is displayed - or you simply set the filter for this component on tProcComp, and you get same list displayed;

In tProjects Table, you can have additional columns, where various totals from Table tProjComp for every project are calculated (essentially what you aseked in this post for).

You can have any number of other report sheets designed (e.g. a report which returns a list of components needed in selected period, along with totals of needed quantities - a simple SUMIFS() will give you those totals).

You design it in this way, and it will work for years for you. You either create an empty copy of it for every year, and fill it with data for this year, or you use same workbook for many years, and whenever it slows down, you delete rows of old data you don't need anymore, or you archive those data in another workbook of similar design, and remove them from working one.

Oops. Found your comment, Pecoflyer, VERY HELPFUL. My thought was simply to suggest an alternative source since the original link apparently failed. Not to criticize. My Bad. (btw: I'm 77)

No worries, it gave Arvi the occasion to exhibit his/her knowledge

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

What I've devised based on @Pecoflyer 's 3D-referencing post works, but it is rather cumbersome.

I appreciate the effort you put into your post. And your solution appears more elegant than my rendition -- but quite beyond my Excel literacy.

Before I attempt to educate myself on Excel defined tables and Validation lists, would you take a moment confirm that your solution should work for my project?

My daughter is a residential construction contractor. I want to estimate her working capital needs by month based on her budget for each "job".

Under my "3D-reference" solution, each job resides on its own sheet. And each job "cell" is an amount by [row] job phase (e.g. Demo, Concrete, Masonry ...) and [column] month staring with January for 24 columns.

Additional rows include "Billing", so as to calculate additional rows "Net" and "Cumulative".

Additional columns include "Phase name" and "Balance Forward"

The "Totals" sheet accumulates all the job sheets (along with other calculations) to establish working capital requirements by month.

My solution works. And serves to meet this month's deadline. So under the adage "If it ain't broke ..." I should probably leave it alone.

But your solution intrigues me. Am I correct that your approach is likely better?

Yes, it will work!

Designed properly, such design allows:
Register project's detailed phases and their budgets/costs monthly (I advice to use month number in format yyyymm);
Register project's start and end at any month;
Register projects of any length (from single month to several years);
In case this is needed, registering both planned budget and real costs spent;
Design monthly and yearly reports for single project, and as total of all projects in report period;
Design reports for totals of selected project (from start to end);
etc.

Based on your post, I assume the main data entry table will be, where planned (and realized) phases of project are registered (E.g. Month, Project, Phase, [Type], Amount, ...). (Type is optional - in case you want enter both planned and realized phases.)

Thank you for your encouraging reply.

As mentioned, I have some self-study ahead of me to understand "defined tables" and "Validation Lists". Is "Register" as you use it above also an Excel keyword concept?

And, dare I hope, is there a internet-viewable example in Excel that you know of that approximates your approach?

"Registering" is user's action - entering an entry, which contains some predefined set of information, into certain table. E.g. when you enter an info about a new project into projects table, you are registering a new project there, and only after that you can use this new project in other tables (the main reason to use Data Validation is to ensure, that entering information for e.g. unregistered entities like projects, periods, etc., is not allowed except the table where this information must be registered first).

Without registering data entities and using Data Validation when adding them into other tables, there will always be the risk of typos, and when user enters e.g. project name into e.g. project components table, and makes any typo (wrong letter, a space at end, etc.), Excel will see it as a separate project in all calculations, plus you will get error whenever some formula tries to get any additional info for it from projects table.

Another reason having separate tables where various information sets are registered (I often use term Registers for such tables) is, this allows to consolidate full information about any information set in a single table. E.g. when you have in projects table columns for start and end months of project, for name of project manager, for name of client, etc., whenever you add this project into other tables, you always can get this additional info read from projects table, instead entering it manually. And whenever you discover, that some of this info must be corrected, you correct it in projects table in single row, and this change affects all tables and reports in your workbook immediately.

I was asking whether "Register" was a Excel keyword / concept or your term.

Thank you for your responses. I certain I could handle this in MySQL, reasonably confident through Excel VBA, but am not familiar with the Excel commands / keywords you reference.

Thanks again