#automating text

Sorry it’s been awhile Excellites, here’s something extra special to make up for it.

 A feature that comes in handy quite often is automating text.

Let’s say we have a situation where text is dependent upon a dropdown that is chosen.

Let’s set up an example that’s close to my heart.

In any cell, type in this formula:

=Today()

That returns the current date.

In another cell set up your dropdown list with Halloween, Thanksgiving and Christmas. You can refer to my post named “A life compiled in lists” or you can watch the video at the end of the tutorial.

  Now we’re going to set up a reference table on Sheet 2.

  Now let’s set up some text that will automatically populate dependent upon what the user chooses.

The trick with automated text is this - Hard coded text is within quotation marks, formulas or cell references are within ampersands, Like so:

  I plug in the = symbol to start the formula and immediately throw in the quotation marks to denote hard coded text. When I’m ready for a formula or for a cell reference, I encapsulate said reference in ampersands and close it out with quotations around the period.

  OK, now let’s make magic. Here’s the formula that I used:

="Johnny counted the days until "&HolidayDropdown&", of which there were "&VLOOKUP(HolidayDropdown,HolidayReferenceTableOnSheet2,2,FALSE)-Today’sDate&"."

And here’s the video!

 How to Automate Text

Text automation comes in handy when you have email or dashboard reports that are handed in frequently. For example, text explaining that your project is over or under budget by a specific amount. Just copy and paste the end result into your email! You only need to set it up once.