1

I have got several excel sheets, every single one of them containing a table for user information and one last sheet which contain the sum of all the previous tables. Since my table is very big, is there a way to create automatically a formula which will sum all the tables of user sheets to the table of sum sheet. The tables are exact in the same position.

A1 = user1!A1 + user2!A1 + user3!A1 + ...+ userN!A1

I'm looking for a better way to select multiple sheets. Additionally, when I add new worksheets for additional users, I want the new worksheets to be included in the calculation without modifieng all formulas manually.

How can I perform what I want?

5
  • It is unclear what you are asking. Is this related to the question you asked yesterday? Have you looked at pivot tables to summarize your data? They are a great tool for that.
    – CharlieRB
    Commented Jul 21, 2015 at 13:34
  • Yes is some, way. I have for example 10 tables like the one I have posted yesterday and I want to create a final one which is the summarization of the ten tables.
    – Jose Ramon
    Commented Jul 21, 2015 at 13:35
  • Is this a different question then? They seem to be the same (duplicate).
    – CharlieRB
    Commented Jul 21, 2015 at 13:37
  • No, it is not the same. In the other question I tried to create a new table which will summarize the table every six months period. I found the use of formula(I am a newbie to excel) and finally summarize every single user table. Now I want to sum every user table that I have to a new one(with exactly the same number of rows and columns). What I am searching is a way to automate the process instead of writing a formula for every cell since the number of cell are up to 200.
    – Jose Ramon
    Commented Jul 21, 2015 at 13:41
  • Investigate using Pivot Tables. Since this is a Q & A site, looking for specific questions, it is not conducive to teaching how to use advance features of excel. That is why I am suggesting you research pivot tables.
    – CharlieRB
    Commented Jul 21, 2015 at 13:47

3 Answers 3

1

Yes you could reference a range of sheets as follows;

A1 =SUM('user1:userN'!A1)

When you add new sheets and want to include them in the formula, you have the following options:

1) Modify your formula using the replace command. On the home tab click Find & Select, Replace. Find what: user3, Replace with: user4. Then click Replace All. If it doesn't work, click Options and by Look in: select Formulas, uncheck Match case and Match entire cell contents. Before you use this option make sure that you don't have the word user3 elsewhere in your sheet besides in your formulas.

2) Add the new sheet before your last referenced sheet. For example when you set your formula user1:user3 add a new sheet after sheet user3 name it lastuser and enter your formula as user1:lastuser. When you want to add a new sheet for an additional user select the lastuser sheet, from the Home tab select Insert, Insert Sheet. This sheet will be inserted before the sheet lastuser and will automatically be included in any user1:lastuser formula.

8
  • This way I have to write a formula for every cell of the table. Is there a way to do this automatically?
    – Jose Ramon
    Commented Jul 21, 2015 at 13:38
  • You can drag this to your entire sheet.
    – User15
    Commented Jul 21, 2015 at 13:49
  • I am not sure that I understand what you mean drag.
    – Jose Ramon
    Commented Jul 21, 2015 at 13:54
  • When you select any cell you'll notice a small squre on the bottem right corner. Move the insertion point over it, click and drag the mouse. It does the same as copy past.
    – User15
    Commented Jul 21, 2015 at 15:28
  • You can also do copy past. You'll notice that when you copy from A1 to B1 the reference will change accordingly. This is called relative reference.
    – User15
    Commented Jul 21, 2015 at 15:30
1

You could use the following formula:

=SUM(user1:userN!A1)

For example: If you have 99 user sheets use:

=SUM(user1:user99!A1)
6
  • This way every time I create a new user I have to change all the cells of the sum table which is a big table.
    – Jose Ramon
    Commented Jul 21, 2015 at 14:04
  • The only why I know of then is to create a custom VBA function. You will pass A1, for example, as a parameter and this function will loop through all sheets that start with user Commented Jul 21, 2015 at 14:09
  • can you be more clear?
    – Jose Ramon
    Commented Jul 21, 2015 at 14:19
  • Here is an example of a custom function aiche.org/chenected/2014/10/… Commented Jul 21, 2015 at 14:22
  • How can I loop through all my sheets?
    – Jose Ramon
    Commented Jul 21, 2015 at 14:34
1

Starting from other answers proposal of using SUM formula I suggest you to create two "blank" worksheets named, for example, "begin" and "end". "begin" must be placed before your first user worksheet ("user1") whereas "end" after your last user ("user99" for example); the formula, then, will be:

=SUM('begin'!A1:'end'!A1)

This way the only thing you must pay attention when adding a new user is make sure the relative "userN" worksheet is placed (after the "begin" and) before the "end" one.

LAST IMPORTANT NOTE: your "sum totals" worksheet must be placed before the "begin" or after the "end" one (otherwise you'll face a circular reference issue).

To be even more more clear here's a sort of "graphic" representation to better give you the idea:

 "TOTALS" | "begin" | "user1" | "user2" | ... | "userN" | "end"

or

 "begin" | "user1" | "user2" | ... | "userN" | "end" | "TOTALS"

ways to order your worksheets are ok.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .