Tags: 1tab, across, cell, consolidate, excel, following, function, indirect, microsoft, msdn, multiple, software, sum, tabs

consolidate data using 3d function AND indirect

On Microsoft » Microsoft Excel

1,893 words with 1 Comments; publish: Fri, 23 May 2008 18:38:00 GMT; (30693.75, « »)

I'm trying to consolidate cell "u1" across multiple tabs using the SUM

function.

The following works for me:

=SUM('tab 1:tab 2'!U1) [resulting in 2 if both u1's are 1]

I know the name of the first tab but the name of the last tab I want to be

determined using the indirect function which will refer to a cell which has

the name of the last tab in it. I've tried the following:

=SUM(INDIRECT("'tab 1:"&$T$5&"'!U1"))

where t5 equals "tab 2", but I get the #REF! error.

Any help wuold be greatly appreciated.

tia,

Dave

All Comments

Leave a comment...

  • 1 Comments
    • well Dave i tryed realy hard but no luck, cant figure that one out

      but i got another solution from mr.bak i think u would find ok

      1. Make a new sheet - name it First - move to leftmost and hide it

      2. Make a new sheet - name it Last - move to rightmost and hide it

      (all new sheet should be inserted betwin those 2 automatic )

      now u can get a sum of all or som of the sheets like

      SUM(First:Last!u1) - gives all sheets u1's

      SUM(sheet3:Last!u1) - gives all value from sheet3 to last

      SUM(First:sheet2!u1) - gives all values from first to sheet2

      pm.

      "Dave Breitenbach" skrev:

      > I'm trying to consolidate cell "u1" across multiple tabs using the SUM

      > function.

      > The following works for me:

      > =SUM('tab 1:tab 2'!U1) [resulting in 2 if both u1's are 1]

      > I know the name of the first tab but the name of the last tab I want to be

      > determined using the indirect function which will refer to a cell which has

      > the name of the last tab in it. I've tried the following:

      > =SUM(INDIRECT("'tab 1:"&$T$5&"'!U1"))

      > where t5 equals "tab 2", but I get the #REF! error.

      > Any help wuold be greatly appreciated.

      > tia,

      > Dave

      #1; Fri, 23 May 2008 18:39:00 GMT