Printable View
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.
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)
Thanks;Quote:
Originally Posted by xld
[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
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().Quote:
Originally Posted by xld