Hello,
Can anyone tell me what is wrong with this formula? Also if you have a better way to achieve the same result that would be fine as well. The formula is supposed to add multiple cells on multiple sheets. I have a drop down cell set up, when the user chooses CAB1 then it would return CAB1's number, if the user chooses CAB2 then it would return CAB1 and CAB2 combined, if the user chose CAB3 then the it would return CAB1, CAB2 and CAB3. I have 9 CAB sheets. I know on older versions of Excel you could only do 7 nested if statements but the newer version can much more and I tested this with 10 nested statements and it worked fine.
Can anyone tell me what is wrong with this formula? Also if you have a better way to achieve the same result that would be fine as well. The formula is supposed to add multiple cells on multiple sheets. I have a drop down cell set up, when the user chooses CAB1 then it would return CAB1's number, if the user chooses CAB2 then it would return CAB1 and CAB2 combined, if the user chose CAB3 then the it would return CAB1, CAB2 and CAB3. I have 9 CAB sheets. I know on older versions of Excel you could only do 7 nested if statements but the newer version can much more and I tested this with 10 nested statements and it worked fine.
Excel Formula:
=if(Combined!M31=Combined!P31,'CAB1'!D2,if(Combined!M31=Combined!P32,'CAB1'!D2+'CAB2'!D2,if(Combined!M31=Combined!P33,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2,if(Combined!M31=Combined!P34,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2,if(Combined!M31=Combined!P35,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!,if(Combined!M31=Combined!P36,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2,if(Combined!M31=Combined!P37,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2,if(Combined!M31=Combined!P38,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2+'CAB8'!D2,if(Combined!M31=Combined!P39,'CAB1'!D2+'CAB2'!D2+'CAB3'!D2+'CAB4'!D2+'CAB5'!D2+'CAB6'!D2+'CAB7'!D2+'CAB8'!D2+'CAB9'!D2,0)))))))))