+ Reply to Thread
Results 1 to 5 of 5

Indirect in combination with ROW

  1. #1
    Nickneem
    Guest

    Indirect in combination with ROW

    If created a formula a while ago in which I refer to sheets with names
    like 1 and 2 --> ROW($1:$2) this way I get a correct amount from the
    sumproduct formula.

    =SUMPRODUCT(SUMIF(INDIRECT("'" & ROW($1:$2) & "'!A2:Z1000"),
    $A4,INDIRECT("'" & ROW($1:$2) & "'!B2:B1000")))

    I want to rename the sheets to something which makes more sense but if
    I try that (like for instance to sheet1) my formula returns an error?

    Thanks in advance,

    Mike


  2. #2
    Harlan Grove
    Guest

    Re: Indirect in combination with ROW

    "Nickneem" <nickneem@gmail.com> wrote...
    >If created a formula a while ago in which I refer to sheets with names
    >like 1 and 2 --> ROW($1:$2) this way I get a correct amount from the
    >sumproduct formula.
    >
    >=SUMPRODUCT(SUMIF(INDIRECT("'" & ROW($1:$2) & "'!A2:Z1000"),
    >$A4,INDIRECT("'" & ROW($1:$2) & "'!B2:B1000")))
    >
    >I want to rename the sheets to something which makes more sense but if
    >I try that (like for instance to sheet1) my formula returns an error?


    Your original formula worked because ROW($1:$2) returned the actual
    worksheet names as an array, {1;2}. If you want to change your worksheet
    names, you'd need to change the formula so that instead of ROW($1:$2) it
    uses something else that returns an array of the worksheet names. Simplest
    would be to enter the new worksheet names in a range, e.g., X99:x100, then
    refer to that range instead.

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$X$99:$X$100&"'!A2:Z1000"),
    $A4,INDIRECT("'"&$X$99:$X$100&"'!B2:B1000")))



  3. #3
    Nickneem
    Guest

    Re: Indirect in combination with ROW

    Thanks Harlan, so if I understand it correct I can't have my sheet
    named like "Inv Japan", "Inv UK", "Inv USA", etc?
    I really have to dig deeper into this sumproduct / indirect thing, I've
    set it up once but now it looks all abracadabra to me after a couple of
    months.

    Thanks for your help!

    Michael


  4. #4
    Harlan Grove
    Guest

    Re: Indirect in combination with ROW

    "Nickneem" <nickneem@gmail.com> wrote...
    >Thanks Harlan, so if I understand it correct I can't have my sheet
    >named like "Inv Japan", "Inv UK", "Inv USA", etc?
    >I really have to dig deeper into this sumproduct / indirect thing, I've
    >set it up once but now it looks all abracadabra to me after a couple of
    >months.


    No. You can name your worksheets anything you want, but unless you name them
    as whole numbers between 1 and 65536 you can't use ROW to generate an array
    of worksheet names.

    If you enter the following into X99:X102,

    Inv Japan
    Inv UK
    Inv USA
    Inv Mars Colony

    and these are names of worksheets with identical layouts, then you can use
    the formula

    =SUMPRODUCT(SUMIF(INDIRECT("'"&$X$99:$X$102&"'!A2:Z1000"),
    $A4,INDIRECT("'"&$X$99:$X$102&"'!B2:B1000")))

    to sum the entries in all of these worksheets' B2:B1000 ranges where the
    corresponding cell in column A of the respective worksheets matches the
    value of cell A4 in the worksheet containing this formula.

    So you just need to replace the ROW(..) call with something that evaluates
    to an array of the names of the worksheets over which you want to sum
    conditionally.





  5. #5
    Nickneem
    Guest

    Re: Indirect in combination with ROW

    Thanks a million Harlan, works perfectly!

    Michael


+ 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