Inserting the value of a cell within a text cell in Excel

Bill Golden
Bill Golden used Ask the Experts™
I have a text cell, for example purposes, A4, that reads:

'Using [   ]Discounted  [   ]Cash Flow  [   ]Both

IF, A1 has an "X" in the cell, I want the line to read...

'Using [ X ]Discounted  [   ]Cash Flow  [   ]Both

IF, A2 has an "X" in the cell, I want the line to read...

'Using [ X ]Discounted  [ X ]Cash Flow  [   ]Both

IF BOTH, A1 and A2 have "X"s in their respective cell, I want the line to read...

'Using [   ]Discounted  [   ]Cash Flow  [ X ]Both

Because of this spreadsheet being used by different versions of Excel, I DO NOT want to use a script or any add-in language.  I know the cell will be somewhat convoluted but it is the best I can do given the task.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Not sure if this works in every version (you said different versions), but this would work for Excel 2007/2010 for sure (I tested it).

=IF(AND(A1="X",A2="X"),"Using [  ]Discounted  [  ]Cash Flow  [ X ]Both",IF(A2="X","Using [ X ]Discounted  [ X ]Cash Flow  [  ]Both",IF(A1="X","Using [ X ]Discounted  [  ]Cash Flow  [  ]Both","Using [  ]Discounted  [  ]Cash Flow  [ X ]Both")))

Open in new window

Bill GoldenExecutive Managing Member


After playing with the formula just a little (the default with nothing checked had Both checked as the default) it worked.  

Thanks, I have two more to work out which I will post if I cannot reason them out with what I learned by your solution.

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