by ·0 Comments

Using Nested If Statements.


Nested IF Function

          A reader asked me to explain logic behind the formula used in my Holiday Email post. That solution involved using multiple IF functions as arguments within another IF function, technique also known as nested IF logic. I find that the best way to use nested if functionality is through using decision trees AND Excel’s VBA syntax. Charting a decision tree will help us better understand the task at hand, and putting various conditions and the ways to handle them in writing, will prepare us to enter necessary formula in Excel.

          Let’s suppose that we need to implement the following logic with 4 possible scenarios. If the value of cell 1 is string “Case 1” AND cell 2 is string “Case 2”, we need the output to be 4. If cell 1 equals “Case 1”, but cell 2 does NOT equal “Case 2”, we need the output to be 3. If cell 1 does NOT equal “Case 1” , but cell 2 equals “Case 2”, the output should be 2. Finally, if cell 1 does not equal “Case 1”, NOR cell 2 equals “Case 2”, the output is 1. Hopefully, using the decision tree below makes it easier to conceptualize these scenarios.
Continue reading

by ·2 Comments

Create a Holiday Email in Excel, using HYPERLINK function


Create Holiday Greetings Email in Excel, using HYPERLINK function

         It seems like this year passed by particularly fast, and now we are at a point when people expect to receive Holiday Greetings from us. Did you know to that you can compose your holiday email entirely in Excel, using the HYPERLINK function? We need to meet two basic requirements: 1) set up a default email client (Outlook, LotusNotes, Thunderbird, GroupWise, etc.,) AND 2) your entire message (including To/CC/BCC/Subject/Body fields) needs to be 255 characters long or shorter. While this method requires no macros, nor VBA code, it cannot actually “send” the email for you, it will open it in your default email client, but you still need to click the SEND button.

          HYPERLINK function has the following syntax (link_location,[friendly_name]). This means that the only required parameter is link_location, passing a sample email address argument, we can create a simple email link using this syntax: =HYPERLINK(“mailto:Email1@example.com”). I would strongly advise to also name your hyperlink, otherwise the cell containing it will appear to be blank. Unfortunately, we can find rather limited documentation on HYPERLINK function within Excel Help section. However, Microsoft did publish additional information about this function. This article can help us easily add To,CC,BCC,Subject, and Body fields to our email template.
Continue reading