Charlie Nichols, Author at Office PowerUps

10Feb 2014 by Charlie Nichols 1 Comment

Editing Text in a SharePoint List View or Data View

InfoPath, SharePoint, SharePoint Designer, Workflow

[wp_ad_camp_1] [wp_ad_camp_4] Having Your Cake and Eating It Too SharePoint text fields (Multi line) have some limitations. In SharePoint, you can enable your text fields (of type Multiple Lines of Text) to keep a running revision history of all the changes in that field. This is great when you need to see the history of comments provided, for example. There are some limitations, however, that come along with that when viewing and editing text in a SharePoint list view. First, you cannot directly view the most recently text entered. You instead see a link to View items which just opens up the associated form for the item where you could see the text field and its history (not really that helpful). If you need to be able to show the most…

29Jan 2014 by Charlie Nichols 2 Comments

How to Get Notified of Workflow Errors in SharePoint or Project Server

Project Server, SharePoint, SharePoint Designer, Workflow

[wp_ad_camp_1] [wp_ad_camp_4] How to Set Up Workflow Error Alerts The process is almost identical for both SharePoint and for Project Server. The only difference is that the URL to the view of the workflow history differs between the two. Open Your Workflow History List The first step in setting up the alert is to navigate to the SharePoint list that has the workflow history. Project Server Workflow History URL <yourserver>/Project%20Server%20Workflow%20History/Allitems.aspx SharePoint Workflow History URL <yourserver>/Lists/Workflow%20History/Allitems.aspx Create Your Custom Workflow History List View Next, create a custom view of the workflow history list. Click Create View on the List tab in the ribbon. Click All History in the Start from an existing view section. Enter a View Name. Call it something like "Workflow Status" or something. Select the following fields (add…

28Jan 2014 by Charlie Nichols 8 Comments

How to Erase or Blank a Text Field with a Workflow in SharePoint Designer

SharePoint, SharePoint Designer, Workflow

[wp_ad_camp_1] [wp_ad_camp_4] Use a Workflow to Erase or Blank a Text Field in SharePoint Sometimes, you come across a situation where you need to erase or blank out the contents of a text field in SharePoint. You don't need to delete the item. Instead, you just need to blank a text field. Using a whitespace character just won't cut it. To accomplish this, you will need to create a simple workflow using SharePoint Designer. Presented below are two specific ways. I've used one of them with SharePoint Designer 2010. I've used the other with SharePoint Designer 2013. How to Blank a Text Field with a Workflow in SharePoint Designer 2013 First, create a workflow variable containing a single space character and use the Trim function to blank the variable. Select…

29Dec 2013 by Charlie Nichols No Comments

SharePoint List Management Tip: Sharing Management of Choices

SharePoint

[wp_ad_camp_1] [wp_ad_camp_4] Sharing the Management of Field Values in a SharePoint List Consider creating separate custom lists to manage the drop-down, radio buttons, or checkbox selections you have in your custom SharePoint lists. I have created custom SharePoint lists for clients. Sometimes I have a “choice” column in the list that has a set of choices that, for numerous reasons, requires the acceptable values to be changed somewhat regularly. As the site administrator or creator of the list I wind up getting frequent requests that require the values in the list to be managed. An individual request is no big deal. But when you start to add up the requests the churn can be somewhat distracting. And toss in an occasional “urgent” change request, and the distraction or randomization factor…

17Dec 2013 by Charlie Nichols 1 Comment

Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Now, Add This New Excel 2013 Trio of Functions to Your Version of Excel Excel 2013 introduced a trio of new functions that make it easy to extend your worksheets to use XML web services. The Excel WEBSERVICE function, ENCODEURL function, and FILTERXML function work nicely together to make the task of working with web services straightforward within Excel 2013. This functionality is made available in Excel 2010, Excel 2007, and Excel 2003 in the Excel PowerUps Premium Suite add-in for Excel. This can be downloaded for free. There are many posts and videos posted on YouTube illustrating how you may use the features and seeing how many there are give you a sense for how popular of an addition these functions are to Excel. The sections below…

11Dec 2013 by Charlie Nichols 1 Comment

Excel Formulas Showing Up as Text

100 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Help! My Worksheet's Excel formulas showing up as text! If you suddenly have Excel formulas showing up as text in your Excel worksheet instead of the results of the formulas, there are a couple of common causes. If you fall into one of these buckets it's a quick fix to get back to normal. Situation 1: You have formula viewing toggled on The easiest thing to try is to toggle the formula view off. You can do this by pressing the CTRL key at the same time as the "`" key. That's the backwards single quote key (in the upper left corner of my keyboard along with the tilde ("~") character. CTRL ` Pressing CTRL+` repeatedly will toggle the formula viewing on/off. Situation 2: You have cells formatted…

09Nov 2013 by Charlie Nichols No Comments

Fuzzy Search in Excel with the Fuzzy Find and Replace Tool

200 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] Fuzzy Search and Replace in Excel You can find similar entries from a list or table in Excel by doing a fuzzy search in Excel. This gives you a way to consider the following to effectively be the same. John Smith Jonathan Smith Smith, John Smith, Jonathan John Q. Smith John Smyth J. Smith Johnny Smith … etc. So if you just want to look for “John Smith” and simply find those entries that are pretty close to that. You want to do a fuzzy text search (not just a wildcard search at the beginning or end of a string). This post describes how to use the Fuzzy Find and Replace feature of the Excel PowerUps add-in for Excel to find those approximate matches. Fuzzy text search in…

23Oct 2013 by Charlie Nichols 2 Comments

Fuzzy VLOOKUP in Excel

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] pwrVLOOKUP. Just like VLOOKUP, only Better. You can do a VLOOKUP operation in Excel and get a fuzzy match instead of being limited to the exact match function built into Excel. To do a fuzzy VLOOKUP you need to use the pwrVLOOKUP function that is part of the Excel PowerUps Premium Suite add-in which is available as a free download on this site. Usage of the pwrVLOOKUP function is very similar to the built-in VLOOKUP function. The first 3 parameters are the same (lookup value, lookup range, and column offset). The difference starts in the fourth parameter. In pwrVLOOKUP, the fourth parameter is a strength-of-match value. It is a number between 0 and 100. If you do not provide this value, an exact match (strenth-of-match = 100) is…

30Sep 2013 by Charlie Nichols No Comments

How to Generate Random Demographic Data

300 level, Excel

[wp_ad_camp_1] [wp_ad_camp_4] How to Generate Random Demographic Data Why Generate Random Demographic Data? There are several reasons I can think of that you would want to or need to generate random demographic data. They primarily center on testing scenarios. Due to the nature of the data, it may be unfeasible for you to access real data to test your application or process. You wouldn’t want to risk exposing PII (Personally Identifiable Information) or other sensitive information. Also, some of the data you may need to test would be restricted or protected under the Sarbanes-Oxley Act (SOX) and/or the Health Insurance Portability and Accountability Act (HIPAA). There are complex regulations that you don’t want to run afoul of, but most of all you want to protect your customer’s sensitive data –…

05Sep 2013 by Charlie Nichols No Comments

SharePoint Workflow Error Occurring (Sometimes)

SharePoint, Workflow

The Problem (and my solution at the bottom) I was having a vexing problem with a set of workflows running on a custom SharePoint list. I would occasionally get a SharePoint workflow error without any consistent reproducible state. Some of the workflows were created by me, and some where there when I took on the role of maintaining the SharePoint site. The Symptoms I Could Observe I was not a site administrator, but I had the ability to create custom lists, workflows, and pages. But I couldn't look at any server logs or anything. The workflows only occasionally failed. Sometimes (most of the time actually), everything worked great. Some of the workflows were set to run when a new record was created, some when a record changed, and some when…