title: Sum() Across Dynamic Number of Sheets
body: <p>Hello all and thanks for your help ahead of time,</p>
  
  <p>I have an excel sheet that is solely to take the summation of multiple sheets. Best and most simply put, the formula is something like <code>=sum(Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1)</code>. There are a few issues that complicate matters though. First off all, I do not know the number or order of the sheets to sum, nor do I know their name. This formula will be copied into ~150 other cells, so I need the summation to be dynamic instead of physically adding sheets to ~150 cells every time. (Also the configuration and naming of the sheet does not allow for easy dragging for formulas.)</p>
  
  <p>So first of all, I thought I could write it using an <code>indirect()</code> reference. I made a column to list all the sheets names that would be added into each cell. Unfortunately, <code>concatenate()</code> cannot be used over arrays, so I had to resort to a UDF seen below:</p>
  
  <pre><code>Function CONCAT(Delimiter As Variant, ParamArray CellRanges() As Variant) As String
  Dim Cell As Range, Area As Variant
  If IsMissing(Delimiter) Then Delimiter = ""
  For Each Area In CellRanges
  If TypeName(Area) = "Range" Then
  For Each Cell In Area
  If Len(Cell.Value) Then CONCAT = CONCAT &amp; Delimiter &amp; Cell.Value
  Next
  Else
  CONCAT = CONCAT &amp; Delimiter &amp; Area
  End If
  Next
  CONCAT = Mid(CONCAT, Len(Delimiter) + 1)
  End Function
  </code></pre>
  
  <p>Using the UDF, I could get a large string with the proper syntax such as <code>=CONCAT("'!A"&amp;(B1+1)&amp;",'",Array_of_Sheets)&amp;"'!A"&amp;(B1+1)</code>. The <code>CONCAT()</code> takes a separator as the first parameter and the array(s) as the second parameter. I then append the "separator" on the end of the string as well to output something such as <code>Sheet1'!A1,'Sheet2'!A1</code>. I thought at this point, a simple <code>=sum(indirect(STRING))</code> would be sufficient, but <code>=sum(indirect("Sheet1!A1,Sheet2!A1,Sheet3!A1,Sheet4!A1"))</code> does not work since <code>indirect()</code> cannot seemingly process the commas.</p>
  
  <p>So to solve this, I switched the <code>=CONCAT()</code> to <code>=CONCAT("'!A"&amp;(B1+1)&amp;"+'",Array_of_Sheets)&amp;"'!A"&amp;(B1+1)</code> to output <code>Sheet1'!A1+'Sheet2'!A1</code>. Now I wrote another UDF to force it to evaluate seen below:</p>
  
  <pre><code>Function EVAL(RefCell As String)
  Application.Volatile
  EVAL = Evaluate(RefCell)
  End Function
  </code></pre>
  
  <p>This worked! Awesome, right? After some use and testing though, it seems to not be quite consistent. When we have other sheets open, it does not always evaluate (and we will have other sheets open). There is some other inconsistencies that I read about with using <code>volatile</code> in the UDF, but I cannot find them.</p>
  
  <p>So for my ideal, I would like to do this without any VBA at all, but I suspect that won't be possible. I would rather not depend on the user to manually recalculate worksheets (partly the reason for <code>volatile</code>). Finally, I just want it to <code>sum()</code> in a consistent manner so if a user opens the document and just hits print, the user does not need to confirm the functions are evaluating correctly (check for #REF errors or manually add up the values to make sure they are correct). I am hoping to find an alternative to using the <code>eval()</code> equation.</p>
  
  <p><em>EDIT for additional information below</em></p>
  
  <p>I had experimented with using 3D arrays, but there was a few issues I ran into. Lets say the workbook has eight sheets. Of those eight sheets, only two may feed into this total sheet. There is a <code>setup</code> sheet, the <code>total</code> sheet, a <code>data entry</code> sheet, and a <code>data analysis</code> sheet. The <code>total</code> sheet totals off of the <code>data analysis</code> sheets. Typically a <code>data entry</code> sheet coincides with a <code>data analysis</code> sheet. So the user may make copies of both the <code>data entry</code> and the <code>data analysis</code> sheets. The most logical order of the sheets would begin with the <code>setup</code> sheet, then each pairing of the <code>data entry</code> and <code>data analysis</code> sheets, and the <code>total</code> sheet. Obviously, this setup will not work with a 3D array. We would have to reorder the sheets and make sure the user knows that the order of the sheets actually matters (an atypical configuration). There will also be instances where one or two <code>data analysis</code> sheets will not be included in the <code>total</code> sheet. So we may end up with a sheet configuration of <code>setup \ data entry (1) \ data entry (2) \ START \ data anaylsis (1) \ data analysis (2) \  END \ totals \ data entry (3) \ data entry (4) \ data anaylsis (3) \ data analysis (4)</code>. My feeling was that a 3D array configuration would almost make the addition <em>too</em> dynamic. I would rather have an explicit list then depend on the user being confident in moving the sheets around to have the proper totals.</p>