Need help with complicated IF statement, possible IFS?

[leopardhawk]

I have a worksheet that will most likely be used by two people (a couple) but could also be used by a 'single' (unmarried) individual.

I'll try to explain my conundrum. Here goes...

The worksheet contains a cell with the following formula and it works as intended but I would like to modify it to be as all-encompassing as possible.

 ="So, when should "&IF(personal_info!C9=0,"Name",personal_info!C9)&" & "&IF(personal_info!C11=0,"Spouse/Partner",personal_info!C11)&" start receiving CPP and OAS payments?  Only "&IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they"))&" can decide."

If C9/C11 and F9/F11 are blank, the formula returns: So, when should Name & Spouse/Partner start receiving CPP and OAS payments? Only they can decide. (this is perfect and I want it to stay like this.)

If C9 contains a name (Joe) and F9 contains an 'M', the formula returns: So, when should Joe & Spouse/Partner start receiving CPP and OAS payments? Only he can decide. (the 'Spouse/Partner' doesn't work here because Joe is single.)

If C9 and C11 contain names (i.e. Joe and Sharon), and F9/F11 contain M/F respectively, the formula returns: So, when should Joe & Sharon start receiving CPP and OAS payments? Only he can decide. (the 'he' doesn't work here because there are two people.)

I would like to modify the formula to do the following:

If C9 and C11 contain names (Joe/Sharon) and F9/F11 contain M/F respectively, the formula would return: So, when should Joe & Sharon start receiving CPP and OAS payments? Only they can decide.

If C9 contains a name (Joe) and F9 contains an 'M' but C11 and F11 are blank, the formula would return: So, when should Joe start receiving CPP and OAS payments? Only he can decide.

If C9 contains a name (Sharon) and F9 contains an 'F' but C11 and F11 are blank, the formula would return: So, when should Sharon start receiving CPP and OAS payments? Only she can decide.

I'm not 100% sure but perhaps the IFS statement would work here but I can't figure out the syntax. Appreciate any and all suggestions!

 

[Fluff]

How about
="So, when should "&IF(personal_info!C9=0,"Name & Spouse/Partner",personal_info!C9)&IF(personal_info!C11=0,""," & " &personal_info!C11)&" start receiving CPP and OAS payments? Only "&IF(personal_info!F11<>0,"they",IF(personal_info!F9="M","he",IF(personal_info!F9="F","she","they")))&" can decide."

 

[leopardhawk]

Fantastic, thanks so much for your help! Works perfectly...

 

[Fluff]

You're welcome & thanks for the feedback