Help with Formula

[imback2nite]

Can anyone help with this formula? The Formula on top works but the bottom Formula gives me an error and highlights the CELL in the last phrase. I've been working on it for awhile and barely have any hair left! Can anyone tell me why? Is it too long?

=IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AV$1,$AV$2=TRUE),'Quarterly Report'!$AV$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AW$1,$AW$2=TRUE),'Quarterly Report'!$AW$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AX$1,$AX$2=TRUE),'Quarterly Report'!$AX$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))="ri",$AV$1="i",$AV$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))="ri",$AW$1="i",$AW$2=TRUE),"ri","")))))

=IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AV$1,$AV$2=TRUE),'Quarterly Report'!$AV$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AW$1,$AW$2=TRUE),'Quarterly Report'!$AW$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))='Quarterly Report'!$AX$1,$AX$2=TRUE),'Quarterly Report'!$AX$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))="ri",$AV$1="i",$AV$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))="ri",$AW$1="i",$AW$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",O12))="ri",$AW$1="i",$AW$2=TRUE),"ri","")))))

 

[imback2nite]

Apologies, THIS formula gets the Error message.

=IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AV$1,$AV$2=TRUE),'Quarterly Report'!$AV$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AW$1,$AW$2=TRUE),'Quarterly Report'!$AW$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AX$1,$AX$2=TRUE),'Quarterly Report'!$AX$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AV$1="i",$AV$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AW$1="i",$AW$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AX$1="i",$AX$2=TRUE),"ri","")))))

 

[StephenCrump]

In both cases, it looks like you're missing a closing bracket ")" at the end.

 

[imback2nite]

In both cases, it looks like you're missing a closing bracket ")" at the end.

Yes. Yes it does. When I input the formula, excel says or points to the CELL function in the last phrase or IF function. But when I get rid of the fifth phrase and leave the sixth phrase, excel takes it and it's fine. There are only 6 nested functions so ... I don't get it.

 

[imback2nite]

Yes. Yes it does. When I input the formula, excel says or points to the CELL function in the last phrase or IF function. But when I get rid of the fifth phrase and leave the sixth phrase, excel takes it and it's fine. There are only 6 nested functions so ... I don't get it.

Well, this seems to work. It's not a nice looking formula but it works...for now. I added the & and then input the rest of the formula. I guess excel thought I had gone over the number of functions I am able to use. ?‍

=IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AV$1,$AV$2=TRUE),'Quarterly Report'!$AV$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AW$1,$AW$2=TRUE),'Quarterly Report'!$AW$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))='Quarterly Report'!$AX$1,$AX$2=TRUE),'Quarterly Report'!$AX$1,IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AV$1="i",$AV$2=TRUE),"ri",IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AW$1="i",$AW$2=TRUE),"ri","")))))&IF(AND(INDIRECT("'"&$AV$4&"'!"&CELL("address",B12))="ri",$AX$1="i",$AX$2=TRUE),"ri","")