Results 1 to 4 of 4

Thread: Alternative to Indirect for relative referenced formula
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2008
    Location
    Melbourne
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Alternative to Indirect for relative referenced formula

    I am seeking to find the Instance of errors in each of 60 columns of data with a variable row limit.

    This formula works for me in one column:

    =SUMPRODUCT(--ISERROR(INDIRECT("AX$7:AX$"&BuildLimit)))

    where BuildLimit is a variable Named Constant say: 2000

    I do not look forward to editing 60 formulas to adjust the Column from A:BH.

  2. #2
    Board Regular
    Join Date
    Sep 2013
    Location
    Blue Mountains, Australia
    Posts
    3,486
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative to Indirect for relative referenced formula

    One alternative: =SUMPRODUCT(--ISERROR(OFFSET(AX7,,,NoOfRows)))

  3. #3
    MrExcel MVP mikerickson's Avatar
    Join Date
    Jan 2007
    Location
    Davis CA
    Posts
    22,648
    Post Thanks / Like
    Mentioned
    20 Post(s)
    Tagged
    15 Thread(s)

    Default Re: Alternative to Indirect for relative referenced formula

    Try

    =SUMPRODUCT(--ISERROR(INDEX($1:$10000, 7 , colNum):INDEX($1:$10000, BuildLimit, colNum)))


    where ColNum is a named value defined by =COLUMN(AX$1)

    As you drag the formula right, the value of colNum will change as will the column being investigated.

  4. #4
    Board Regular
    Join Date
    Jun 2008
    Location
    Melbourne
    Posts
    78
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Alternative to Indirect for relative referenced formula

    Quote Originally Posted by mikerickson View Post
    Try

    =SUMPRODUCT(--ISERROR(INDEX($1:$10000, 7 , colNum):INDEX($1:$10000, BuildLimit, colNum)))


    where ColNum is a named value defined by =COLUMN(AX$1)

    As you drag the formula right, the value of colNum will change as will the column being investigated.
    Thank You, gentlemen. That is totally workable and both techniques have merit.

    Brendan

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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