Posts by herilane

Final notes and comments

Since this technique utilises XLM macros, Excel 2003 users will get a warning message when opening a file that uses this. The technique will not work if XLM macros are disabled in the file.

The XLM macro language is localized. If you have non-English Excel, you will need to translate not only the worksheet functions, but the XLM formulas as well. Luckily, existing XLM formulas get converted when opened in another version of Excel, so you can use the sample workbook here to find out what you local equivalent of “GET.WORKBOOK” might be. Alternatively, get your local version of xlmacro.exe at Microsoft's download site).

I hope you will find this formula useful, and that my explanation has shed some light on how it works.

Special thanks to member Insomniac for inspiration, corrections, suggestions, and help with testing this.

Step 5 - putting it all together

Almost there now!

We only need to get rid of that error value, and sum the other values. We also need to take into account the possibility that there are chart sheets in the workbook, and exclude those.

We will use ISERROR to get rid of any invalid addresses, and INDIRECT() and N() to sum the valid values, as before:

=SUM(IF(ISERROR(N(INDIRECT(Addr))),0,N(INDIRECT(Addr))))

Bingo! The formula now sums the values in the current cell across all worksheets, excluding the calling sheet.

If you want to make the worksheet easier to read at a glance, you can create a second named formula – name it ThreeDSum for example – that refers to the SUM() formula we’ve just created. In your worksheet, you would then simply enter “=ThreeDSum” as the formula.

Step 4 - XLM

Now we “just” need to get rid of the hard-coded list of sheets.

For this, Excel’s worksheet functions are no longer enough, and we need more powerful tools – namely XLM macros. XLM is Excel’s old macro language that predates VBA. For backwards compatibility, XLM functions can still be used in new versions of Excel.
XLM functions are not covered by the Excel help files, but you can download the help files for XLM functions from http://www.microsoft.com/downloads/ – search for macrofun.exe, or for xlmacro.exe. The latter was last spotted at http://www.microsoft.com/downl…03748ca5cd&displaylang=en

We are going to use two functions here: GET.WORKBOOK and GET.DOCUMENT.

• GET.WORKBOOK returns information about the current workbook. GET.WORKBOOK(1) will return a list of all worksheets in the workbook.
• GET.DOCUMENT returns information about the current document, which in the case of worksheet formulas means the current sheet. GET.DOCUMENT(1) returns the sheet name.

The main limitation of XLM functions is that they can only be used in named formulas, not directly in a worksheet. So we can break down our formula into two parts – a named formula to replace SheetList, and an array formula like what we had before.
So let us try named formula, named Addr:

=GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN())

Try entering it in a worksheet range (I entered it in cells A8 to D8) and you should get results similar to this (where // separates cells):

[3d sum.xls]Totals!$A$8 // [3d sum.xls]Sheet1!$B$8 // [3d sum.xls]Sheet2!$C$8 // [3d sum.xls]Sheet3!$D$8

The problem with this is that our Totals sheet is included in the results. If we used this in a formula, the calculation would be circular. We need to exclude the current sheet from the array.

This is where the GET.DOCUMENT function comes in. We modify our formula so that the current sheet is excluded, and replaced with a blank:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"",GET.WORKBOOK(1)&"!"&ADDRESS(ROW(),COLUMN()))

Used in a worksheet formula, the named formula should now yield something like this: (the first cell is empty)

// [3d sum.xls]Sheet1!$B$8 // [3d sum.xls]Sheet2!$C$8 // [3d sum.xls]Sheet3!$D$8

Now, if we tried to use any of these range references in an INDIRECT function, we would still get an error, because of the space in the workbook name. To handle that, we need to put single quotes before and after the workbook / worksheet names:

=IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"","'"&GET.WORKBOOK(1)&"'!"&ADDRESS(ROW(),COLUMN()))

This should return:

// '[3d sum.xls]Sheet1'!$B$8 // '[3d sum.xls]Sheet2'!$C$8 // '[3d sum.xls]Sheet3'!$D$8

Step 3- ADDRESS()

Of these two issues, let us attack the easier one first – let us get rid of the hard-coded cell references.

For this, Excel has an ADDRESS() function that returns the address of a cell based on a row number and a column number. Combine that with the ROW() and COLUMN() functions, and we can get the address of the current cell:

=ADDRESS(ROW(), COLUMN())

This returns $A$1 for A1, etc.
Put that in the INDIRECT formula, and we get this array formula:

=SUM(N(INDIRECT(SheetList&"!"&ADDRESS(ROW(),COLUMN()))))

This can be put in any cell, and will return the sum of that cell across all sheets in the SheetList.

Step 2 - INDIRECT

Step two: let us make that formula flexible, so that we can easily adjust it if we change our mind about the sheet order. The plan is to use INDIRECT and later replace the hard-coded sheet names in the formula with references to named ranges. So let us try this array formula:

=SUM(INDIRECT("Sheet1:Sheet3!A1"))

The formula looks perfectly OK, but doesn’t work – the result is a #REF! error. For some reason, INDIRECT simply refuses to work with 3D ranges.
(Note that all formulas from here on are array formulas, and should be entered using Ctrl+Shift+Enter, not just Enter.)

Undeterred, let us test a modification, which at first looks like it’s taking us in the wrong direction. Let us create a list of all the sheets we want to sum, and use that as an input to the INDIRECT function. I named my range with the sheet list “SheetList”, and tried this array formula:

=SUM(INDIRECT(SheetList&"!"&"A1"))

This didn’t give an error, but Excel seemed to have trouble with adding, since it claimed that 1+2+3=1… As an experiment, move the sheets around a bit, and it will be clear that INDIRECT is only “adding” the first sheet.
Luckily, the Excel Experts E-letter (back issues of which can be found here http://www.j-walk.com/ss/excel/eee/) reports that the N() worksheet function can induce INDIRECT to treat the sheet list properly, as an array. The following array formula correctly returns 6:

=SUM(N(INDIRECT(SheetList&"!"&"A1")))

This is much better, since the results no longer depend on the sheet order. However, we still need to maintain a list of sheet names, and the formula is only summing A1 and not all the cells we want.

Step 1 - the basics

The first step towards our goal is a simple 3D formula:

=SUM(Sheet1:Sheet3!A1)

This is nice, and returns 6 as it should, but only works if the sheet order doesn’t change. Move Sheet3 to before Sheet1, and the formula will no longer sum all sheets. We need more flexibility than that.

Explanation

The named range returns a list of cell references for all sheets, excluding the calling sheet. It adds single quotes around the name of the workbook and worksheet, in case there are spaces in the workbook / worksheet names. Used in cell A1 in a sheet, it will return an array like this:
“ ”, “'[book1.xls]Sheet1'!$A$1”, “' [book1.xls]Sheet2'!$A$1”, etc.

The array formula uses INDIRECT to get the values of those cells. It then uses N() to convert the results of INDIRECT into proper arrays. Next it uses ISERROR to get rid of the error caused by the first, empty cell reference, as well as references to chart sheets. Finally, SUM to sum it all up.

The rest of the thread will explain the reasoning behind this, step by step. The example file I used for this has 4 sheets: Sheet1 through Sheet3 that hold the values, and Totals that will hold the sum. I filled Sheet1 with 1s, Sheet2 with 2s and Sheet3 with 3s.

The solution

If you just want the answer, here it is:

• Define a named range – I called it Addr:
  

  
  

  =IF(GET.WORKBOOK(1)=GET.DOCUMENT(1),"","'"&GET.WORKBOOK(1)&"'!"&ADDRESS(ROW(),COLUMN()))

  
  

  Note the use of single quotes before and after GET.WORKBOOK(1).

  
  

  
  

• Use the following array formula in any sheet to get the sum of the same cell across all other sheets:
  

  
  

  =SUM(IF(ISERROR(N(INDIRECT(Addr))),0,N(INDIRECT(Addr))))

How can we calculate the sum of cell A1 across all sheets in the workbook, without limitations such as having the sheets in the correct order, or naming sheets in a particular way? It’s easy enough to write an UDF that does the trick. But a formula solution has eluded us for years, even though I've searched far and wide. Finally, it looks like this problem has been solved!

The principles involved are simple, but a whole lot of components are involved – the final solution involves the INDIRECT function, XLM functions, named formulas and array formulas. This thread will walk you through the whole reasoning behind the solution, step by step. There is also a sample workbook with both the final solution, and all the intermediate steps.

I originally wrote this for http://www.thecodenet.com/ but WillR has kindly invited me to post this here as well. Thanks Will.

Sorry, yes, I forgot to mention one key part of it It's been a couple of days since I looked at this.

I added one extra column, between A and B, with zeros in it - for "Lev1" so to say. So my formula actually belongs in column P.

Let me know if you want me to e-mail it to you.

Here's mine:

=SUM(((C4:H4<$A4)*IF(C4:H4=0,0,(C4:H4-B4:G4))+((B4:G4<=$A4)*(C4:H4&gt;$A4)+(C4:H4=0)*(B4:G4<>0))*($A4-(B4:G4)))*(rngBase+I4:N4))+IF(AND(H4&gt;0,A4&gt;H4),(A4-H4))*(rngBase+O4)

Or try this array formula (assuming there are no ties):

=MAX((A3:E8=MIN(A3:E8))*COLUMN(A3:E8))

I think I may have a solution for you... it's a monster of a long and unreadable array formula and only requires 1 extra column to be added.

Before I translate, post and try to explain it, would you mind confirming that the results I get for your sample data are what you would expect:

152.50
539.99
6521.92
632.52
1643.67
424.67

OK, in that case let's try this modification:

=SUM (IF(COUNTIF(A3:C3 , {1;2;3;4;5;6;7;8;9;0}) &gt;0 , IF(COUNTIF(A2:C2 , {1;2;3;4;5;6;7;8;9;0}) &gt;0 , 1 , 0)))

This counts the number of times each digit occurs in both ranges. If both are greater than zero (i.e. the digit is present in that range), return 1 for that digit, else return 0.

Try this array formula... (you'll need to change the ranges, I've made this for data in A1 to C2)

Your question isn't entirely clear about the requirements... For example, if the data is (2, 2, 5), (2, 4, 2), do you want the result to be 1 (only 1 distinct digit is repeated, "2") or 2 (2 digits are the same, "2" and "2"). I've assumed you want 2 to be returned in that case.

=SUM(IF(COUNTIF(A2:C2,{1;2;3;4;5;6;7;8;9;0}) < COUNTIF(A1:C1,{1;2;3;4;5;6;7;8;9;0}) , COUNTIF(A2:C2,{1;2;3;4;5;6;7;8;9;0}) , COUNTIF(A1:C1,{1;2;3;4;5;6;7;8;9;0})))

(If you get an error, it's probably because there's a ";" in there instead of a "," because of my regional settings - I tried to replace them all but may have missed one somewhere)

[shameless self-promotion]
Or take a look at this introduction to userforms:
http://www.visualbasicforum.com/t131000.html
[/shameless self-promotion]

Is the data always in groups of three rows?

Click Debug when the error message comes up, and see which line is highlighted.

Try .Range.Formula rather than just .Range

In what line of code does the error occur?