Modifying IF statement formula to align with deletion of tabs and addition of new tab

Hello,

I am modifying the existing formula with the new insurance tab "BCBS CARE FIRST VA.MD.DC", there are 3 tabs Aetna, Cigna, and United which all three will be deleted and there will be one tab "BCBS CARE FIRST VA.MD.DC". Could you please help me to modify this original formula to include only 1 tab in the model "BCBS CARE FIRST VA.MD.DC"?

All the ranges are fine I just need to figure what to deleted and replace with "BCBS CARE FIRST VA.MD.DC"

[=IF(AK2="AETNA", IF(AL2="ACA OON", IF(AT2="PPO", 'BCBS CARE FIRST VA.MD.DC'!$K$39, 'BCBS CARE FIRST VA.MD.DC'!$K$40), IF(AT2="PPO",'BCBS CARE FIRST VA.MD.DC'!$K$36, 'BCBS CARE FIRST VA.MD.DC'!$K$37)), IF(AK2="Cigna", IF(AL2="ACA OON", IF(AT2="PPO", 'Cigna-FOR REFERENCE'!$K$39, 'Cigna-FOR REFERENCE'!$K$40), IF(AT2="PPO",'Cigna-FOR REFERENCE'!$K$36, 'Cigna-FOR REFERENCE'!$K$37)), IF(AK2="United", IF(AL2="ACA OON", IF(AT2="PPO",#REF!,#REF!), IF(AT2="PPO",#REF!,#REF!)), "ERROR")))*IF(AK2="AETNA", IF('BCBS CARE FIRST VA.MD.DC'!$A$24="All States", BG2, IF('EOM Data'!AH2='BCBS CARE FIRST VA.MD.DC'!$B$24, 1, 0)), IF(AK2="CIGNA",IF('Cigna-FOR REFERENCE'!$A$24="All States", BG2, IF('EOM Data'!AH2='Cigna-FOR REFERENCE'!$B$24, 1, 0)), IF(AK2="UNITED",IF(#REF!="All States", BG2, IF('EOM Data'!AH2=#REF!, 1, 0)), "ERROR" )))]


Thanks,

Pigment01

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Click on Edit to open your thread, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)