View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Toppers Toppers is offline
external usenet poster
 
Posts: 4,339
Default Sum across worksheets with indirect

Try:

Add worksheet "First" before first in your w/sheet range and "Last" at end
of range.

In worksheet with formula (outside w/list I assume):

=SUM(First:Last!D16)

HTH

"Tom Hayakawa" wrote:

Hello All,

I'm trying to get the sum of values in the same cell across a range of
several worksheets. The range of the worksheets can change over time, as
well. For instance, the formula SUM('1:2'!D16) might also need to be
SUM('21:26'!D16). The worksheets will always be in a contiguous range. I'm
trying to use the INDIRECT function to get the worksheet numbers into the SUM
formula, but I'm not having much luck. For instance, this is what I tried,
but it doesn't work:

=SUM(INDIRECT(CONCATENATE("'",E3,":",F3,"'!D16")))

where E3 is the first worksheet and F3 is the last worksheet in the range.
Any ideas on how to do this so it works? Thanks in advance.