"Elkar" wrote...
....
=IF(OR($E12={"","(Show All)","(All)"}),
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j7" ))),"",
INDIRECT("'"&F12&"'!j7")),"")&IF($E12="Base",
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j8" ))),"",
INDIRECT("'"&F12&"'!j8")),"")&IF($E12="Investme nt Projects",
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j9" ))),"",
INDIRECT("'"&F12&"'!j9")),"")&IF($E12="Corporate" ,
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j10 "))),"",
INDIRECT("'"&F12&"'!j10")),"")&IF($E12="Inter-Branch Charging",
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j11 "))),"",
INDIRECT("'"&F12&"'!j11")),"")&IF($E12="CBSA",
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j12 "))),"",
INDIRECT("'"&F12&"'!j12")),"")&IF(AND($E12<{""," (Show All)","(All)",
"Base","Investment Projects","Corporate","Inter-Branch Charging","CBSA"}),
IF(ISERROR(CELL("address",INDIRECT("'"&F12&"'!j13 "))),"",
INDIRECT("'"&F12&"'!j13")),"")

Instead of nesting the formulas inside one another, I just concatenated
several IFs together. Since its impossible for more than one of them to
be TRUE at the same time, this method works.

Better to use some lookups. And it's not necessary to check the precise cell
reference, just check that F12 points to a worksheet in some open workbook.

=IF(ISERROR(CELL("Address",INDIRECT("'"&F12&"'!J1" ))),"",
IF(OR(COUNTIF($E12,{"","(All)","(Show All)","Base","CBSA","Corporate",
"Inter-Branch Charging","Investment Projects"})),INDIRECT("'"&F12&"'!J"&
LOOKUP($E12,{"","(All)","(Show All)","Base","CBSA","Corporate",
"Inter-Branch Charging","Investment Projects"},7,7,7,8,12,10,11,9})),
INDIRECT("'"&F12&"'!J13")))