Including text from another cell in a formula

Bill Golden
Bill Golden used Ask the Experts™
on
I have the following formula in a cell

=IF(AND(D17="X",F17="",H17="",J17="",L17=""),"Who is the "&E17&" of "&Q8&" and we are valuing ",IF(AND(D17="",F17="X",H17="",J17="",L17=""),"Who is a "&G17&" in "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="X",J17="",L17=""),"Who is a "&I17&" in "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="",J17="X",L17=""),"Who is an "&K17&" of "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="",J17="",L17="X"),"Who is a "&M17&" of "&Q8&" and we are valuing","")))))

and I want to add the text contents of cell AA18 after each phrase ...we are valuing.... I have included the worksheet. Thanks in advance. (For those of you who are following my progress, the tumor has shrunk by 50% and we are only 25% through the Proton Therapy. They are carving this thing up like a Christmas turkey! Chemo is hell but I still have my hair.)
EE-Snippet-CalcXLM-Report-Rev-25H-T.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
How about a simple search and replace? (extreme right of Home tab)

Find: valuing"
replace with: valuing" & aa18

Options:  formulas
Top Expert 2016

Commented:
Hi,

pls try

=IF(AND(D17="X",F17="",H17="",J17="",L17=""),"Who is the "&E17&" of "&Q8&" and we are valuing ",IF(AND(D17="",F17="X",H17="",J17="",L17=""),"Who is a "&G17&" in "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="X",J17="",L17=""),"Who is a "&I17&" in "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="",J17="X",L17=""),"Who is an "&K17&" of "&Q8&" and we are valuing ",IF(AND(D17="",F17="",H17="",J17="",L17="X"),"Who is a "&M17&" of "&Q8&" and we are valuing"&" "&AA18,"")))))

Open in new window

Regards
Microsoft Excel Expert
Top Expert 2014
Commented:
Bill GoldenExecutive Managing Member

Author

Commented:
regmigrant, I could not get your formula to save and Rgonzo1971 your solution returned a strange 0 centered on the bottom line.  Professor I had to add the syntax on the last selection but it worked perfectly.  Thanks
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
you are welcome. i am glad it worked.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial