+ Reply to Thread
Results 1 to 3 of 3

Sum Indirect function through multiple sheets

  1. #1
    Andre Croteau
    Guest

    Sum Indirect function through multiple sheets

    Hello!

    I have been looking through Googgle for a solution but have yet to find it.

    I am trying to replicate the following 3 dimentional sum formula using the
    indirect function

    =SUM(START:END!A1:A5)


    Suppose I have the following (without the double quotes):

    in cell C1 I have the label "START"
    in cell D1, I have the lable "END"
    in cell E1 I have "A1"
    in cell F1 I have "A5"

    These are some of examples that I tried in cell B1

    =SUM(INDIRECT("'"&C1&":"&D1&"'!"&E1&":"&F1))
    =SUM(INDIRECT(C1&":"&D1&"!"&E1&":"&F1))
    =SUM(INDIRECT(C1&":"&D1&"!A1:A5"))

    For each of these trial formulas, I ended up with a #REF! result
    I have yet to see an example with he indirect function used over multiple
    sheets.

    What am I going wrong?

    Thanks in advance!

    André



  2. #2
    Domenic
    Guest

    Re: Sum Indirect function through multiple sheets

    Try...

    =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C10&"'!A1:A5"),"<>"))

    ....where C1:C10 contains your sheet names.

    Hope this helps!

    In article <OPvee.4030$31.2745@news-server.bigpond.net.au>,
    "Andre Croteau" <milandre@bigpond.net.au> wrote:

    > Hello!
    >
    > I have been looking through Googgle for a solution but have yet to find it.
    >
    > I am trying to replicate the following 3 dimentional sum formula using the
    > indirect function
    >
    > =SUM(START:END!A1:A5)
    >
    >
    > Suppose I have the following (without the double quotes):
    >
    > in cell C1 I have the label "START"
    > in cell D1, I have the lable "END"
    > in cell E1 I have "A1"
    > in cell F1 I have "A5"
    >
    > These are some of examples that I tried in cell B1
    >
    > =SUM(INDIRECT("'"&C1&":"&D1&"'!"&E1&":"&F1))
    > =SUM(INDIRECT(C1&":"&D1&"!"&E1&":"&F1))
    > =SUM(INDIRECT(C1&":"&D1&"!A1:A5"))
    >
    > For each of these trial formulas, I ended up with a #REF! result
    > I have yet to see an example with he indirect function used over multiple
    > sheets.
    >
    > What am I going wrong?
    >
    > Thanks in advance!
    >
    > André


  3. #3
    Andre Croteau
    Guest

    Re: Sum Indirect function through multiple sheets

    Hello Dominic,

    It works well!

    It's just a bit surprising that one must revert to a sumproduct formula!

    Thanks a lot!

    André



    "Domenic" <domenic22@sympatico.ca> wrote in message
    news:domenic22-92BF6E.21183705052005@msnews.microsoft.com...
    > Try...
    >
    > =SUMPRODUCT(SUMIF(INDIRECT("'"&C1:C10&"'!A1:A5"),"<>"))
    >
    > ...where C1:C10 contains your sheet names.
    >
    > Hope this helps!
    >
    > In article <OPvee.4030$31.2745@news-server.bigpond.net.au>,
    > "Andre Croteau" <milandre@bigpond.net.au> wrote:
    >
    > > Hello!
    > >
    > > I have been looking through Googgle for a solution but have yet to find

    it.
    > >
    > > I am trying to replicate the following 3 dimentional sum formula using

    the
    > > indirect function
    > >
    > > =SUM(START:END!A1:A5)
    > >
    > >
    > > Suppose I have the following (without the double quotes):
    > >
    > > in cell C1 I have the label "START"
    > > in cell D1, I have the lable "END"
    > > in cell E1 I have "A1"
    > > in cell F1 I have "A5"
    > >
    > > These are some of examples that I tried in cell B1
    > >
    > > =SUM(INDIRECT("'"&C1&":"&D1&"'!"&E1&":"&F1))
    > > =SUM(INDIRECT(C1&":"&D1&"!"&E1&":"&F1))
    > > =SUM(INDIRECT(C1&":"&D1&"!A1:A5"))
    > >
    > > For each of these trial formulas, I ended up with a #REF! result
    > > I have yet to see an example with he indirect function used over

    multiple
    > > sheets.
    > >
    > > What am I going wrong?
    > >
    > > Thanks in advance!
    > >
    > > André




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1