SharePoint List Management Tip: Sharing Management of Choices

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…
Read More
Excel WEBSERVICE Function, plus ENCODEURL and FILTERXML Make a Useful Trio of Web Functions

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…
Read More
Excel Formulas Showing Up as Text

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…
Read More
Fuzzy Search in Excel with the Fuzzy Find and Replace Tool

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…
Read More
Fuzzy VLOOKUP in Excel

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…
Read More
How to Generate Random Demographic Data

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 –…
Read More
SharePoint Workflow Error Occurring (Sometimes)

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…
Read More
Date Values in Excel Explained

Date Values in Excel Explained

100 level, Excel
Date values in Excel are numbers In Excel, a date is the number of days since January 1, 1900 starting with January 1, 1900 being “1”. Each date after that, Excel adds one more number to that sequence. So August 26, 2013 is 41512, or 41,512 days since January 1, 1900. The integer part of the number is used for the days. The decimal part of the number is the fractional part of the day -- or the time. So .5 would be 50% of the way thru the day, or 12:00 noon. That makes 41,512.5 to be equivalent to 12:00 noon on August 26, 2013. The date number pattern is a follows. DDDDD.TTTTTT So as you might imagine, adding 3 to the date value of August 30, 2013 results…
Read More
Percentage Change Between Two Numbers

Percentage Change Between Two Numbers

200 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] I was asked what the Excel function was for finding the percentage of change between two numbers in Excel. It turns out there is no dedicated function for that. That's OK. The formula for calculating the change between two numbers in Excel is really simple (and there is one less function in Excel that you'd need to learn). The formula for % change between two numbers The simple formula is shown below. =(A2/A1)-1 In this example, cell A2 contains the new number and cell A1 contains the original number. The formula will return a value that represents the percentage change between the two numbers. Negative changes will show up as negative numbers. If you want to see the value as a percentage, just click the percent sign button "%" in…
Read More

How to Open Excel Files in Separate Windows Automatically

400 level, Excel
  I was asked how set up Excel so that opening individual Excel files would open in separate Excel Windows. By default, it seems Excel 2007 and 2010 open separate files in the same instance of Excel. In technical terms, Excel uses MDI instead of SDI. I searched and found several posts that basically said "can't be done" or "not supported". I found some others that outlined some Registry tweaks. They were slightly different in their instructions and a little hard to follow. I tried some of the changes and have documented what has worked for me on multiple systems with different versions of Windows as well as Excel. Setting up your system to open Excel files in separate windows If you have Excel 2007 or Excel 2010, you can…
Read More