PDA

View Full Version : Solved: Need a quick Indirect() reference



stanl
02-05-2013, 12:29 PM
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.

Bob Phillips
02-05-2013, 02:40 PM
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(INDIRECT("'"&A98&"'!H1:H150"),">0"))

If you have Excel 2007 on, you can simplify it

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

stanl
02-05-2013, 02:48 PM
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

Bob Phillips
02-06-2013, 05:14 AM
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.

stanl
02-06-2013, 05:42 AM
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().