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().
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.