Consulting

Results 1 to 5 of 5

Thread: Solved: Need a quick Indirect() reference

  1. #1
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location

    Solved: Need a quick Indirect() reference

    Assuming a formula in Cell B98

    =IF(ISERROR(AVERAGEIF('MYTab'!H1:H150,">0")),0,AVERAGEIF('MYTab'!H1:H150,"> 0"))


    where MyTab is a tab name, but Cell A98 has the text "MyTab". How would I use Indirect() to reference cell A98 in the formula in B98. I have tried stuff like

    =IF(ISERROR(AVERAGEIF(Indirect(&"A98"&!H1:H150),">0")),0,AVERAGEIF(Indirect ("&A98&"!H1:H150),">0"))


    but appear to be syntactically challenged.

    Appreciate any pointers.

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Stan,

    the cell with the reference data is left as is, and the referenced cells are in quotes. I also added single quotes around the sheet name in case it has spaces

    =IF(ISERROR(AVERAGEIF(INDIRECT("'"&A98&"'!H1:H150"),">0")),0,AVERAGEIF(INDI RECT("'"&A98&"'!H1:H150"),">0"))

    If you have Excel 2007 on, you can simplify it

    =IFERROR(AVERAGEIF(INDIRECT("'"&A98&"'!H1:H150"),">0"),0)
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by xld
    Stan,
    If you have Excel 2007 on, you can simplify it

    =IFERROR(AVERAGEIF(INDIRECT("'"&A98&"'!H1:H150"),">0"),0)
    Thanks;

    [EDIT]: so now assuming there are 97 previous rows that need the same formula is there a way to make indirect 'generic' as the value in Cell A(n=1 to 98), so that if the initial formula is placed in cell B1 it can be copied to cell B98

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Which part do you want to change in each row Stan? If it is A98, that will change with a simple drag copy as it is not absolute.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    VBAX Master stanl's Avatar
    Joined
    Jan 2005
    Posts
    1,141
    Location
    Quote Originally Posted by xld
    Which part do you want to change in each row Stan? If it is A98, that will change with a simple drag copy as it is not absolute.
    Big Thanks. You re-learn something new every day. I've spent the last year just dumping data from SQL Server, Access, Oracle into Excel and not been that involved with functions like Indirect().

Posting Permissions

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