21Oct 2014 by Charlie Nichols No Comments
Return Random Value from List in Excel

Return Random Value from List in Excel

200 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Two Functions to Make Returning Random Data Easy Using the pwrRANDFROMSET and pwrRANDFROMRANGE functions it's easy to get a random value back to your formula. Random Values from a Set The pwrRANDFROMSET function returns a value from a set defined within the function while the pwrRANDFROMRANGE function returns a value from a defined range in Excel. If you wanted to return a random value from a set of colors you can use the equation below as an example. =pwrRANDFROMSET("red", "pink", "green", "blue", "black", "orange", "yellow") This will return a random value from the set of colors in the formula. Random Values from a Range The pwrRANDFROMRANGE function returns a value from a range of cells. This range can be multiple columns and rows as long as they are…
Read More
24Sep 2014 by Charlie Nichols No Comments
How to Collect Excel User Input and Return to Formulas

How to Collect Excel User Input and Return to Formulas

300 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Collecting Excel User Input Using the Excel PowerUps Premium Suite you can easily collect basic user input and return that input to your formula. You can use the function called pwrINPUTBOX to prompt the user to provide some information. Since this is a function made available to you in Excel, you don't need to write any code. Example Here's an example. Imagine you need to have the user provide some information if a value is beyond a given threshold. Let's say if a key performance metric falls below a specific level you want to capture a brief note regarding why from the user. So let's say we want to capture an explanation if the KPI falls below 85%, and that the KPI is in cell A1. The formula…
Read More
24Sep 2014 by Charlie Nichols 2 Comments
How to Show an Excel Message Box or Alert without Code

How to Show an Excel Message Box or Alert without Code

300 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Show an Excel Message Box Occasionally, you need to alert the user of a specific condition or value in your Excel workbook. For example, if a value gets higher or lower than a desired level you may wish to make that more apparent to the user. You can use the Conditional Formatting features in Excel to, say, make the field bright red. However, you would be relying on the user to view that tab or potentially scroll to that part of the worksheet to actually see the value in the first place. Alternatively, you can pop up a message box to alert the user of the condition or value. One method is to use the embedded scripting system within Excel and write some custom code to pop up…
Read More
17Sep 2014 by Charlie Nichols No Comments
Latest Excel PowerUps Add-In Update: Version 1.7.1 Adds User Input

Latest Excel PowerUps Add-In Update: Version 1.7.1 Adds User Input

Excel
A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new functions. Go to the download page. New Functions pwrCONTAINS pwrCONTAINS has been added to make it very easy to check whether a one string is contained within another string. pwrINPUTBOX pwrINPUTBOX provides a way to gather user input from within a formula. You can use the text the user provides in the formula or simply display. pwrMESSAGEBOX pwrMESSAGEBOX provides a way to alert on the conditions you define in your formulas. If a value in another tab or off-screen reaches a trigger level, for example, you can use pwrMESSAGEBOX to alert the user of the condition rather than waiting for the user to scroll or tab over and see the value…
Read More
26May 2014 by Charlie Nichols No Comments
Excel Add-in Update: Version 1.6.4 Adds New Capability

Excel Add-in Update: Version 1.6.4 Adds New Capability

300 level, Excel
A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. Enhanced Functions pwrFILTERXML pwrFILTERXML has been extended to directly open XML files or call XML web services. This allows pwrFILTERXML to work without having to first call the WEBSERVICE function in another cell. This also means you will not be limited by Excel's 32k per cell character limit. This is key for utilizing web services that return more than 32k of text. pwrWEBSERVICE pwrWEBSERVICE has also been extended to directly open XML files. This allows you to reference local XML files. You will still be limited by Excel's built-in 32k limit per…
Read More
24May 2014 by Charlie Nichols No Comments
Work Beyond Excel’s 32k Per-Cell Character Limit

Work Beyond Excel’s 32k Per-Cell Character Limit

300 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] The Character Limit in Excel can be Limiting - Don't let it be There is a character limit in Excel. Excel has a character limit (per cell) of 32,767 characters. And in most cases this is ample space for your needs. Sometimes You Need to Work with XML Blocks Larger Than 32k. You may have a need that resembles one of the following scenarios. You need to consume a web service that returns more than the 32k limit. You need to open a local XML file, and that file may also be larger than the 32k character limit. Meet New and Improved WEBSERVICE and FILTERXML Functions Each of the functions, including their “power” equivalent functions of pwrWEBSERVICE and pwrFILTERXML, has been enhanced in the latest version of the Excel…
Read More
28Apr 2014 by Charlie Nichols 8 Comments
How to Bulk Import to SharePoint from Excel with a Choice Field

How to Bulk Import to SharePoint from Excel with a Choice Field

400 level, Excel, SharePoint, SharePoint Designer, Workflow
[wp_ad_camp_1] [wp_ad_camp_4] Scenario: Import to SharePoint from Excel Do you have a large SharePoint list with dozens, hundreds, or even thousands of items? And, are some of the columns in your list choice fields based on lookup values from other SharePoint lists? If you have data from an Excel file that you need to import to SharePoint from Excel, you’ll quickly find that a straightforward copy and paste operation won’t cut it. However, with a bit of prep work you can absolutely do a bulk import of your data without having to write code, or buy a special add-in. Setting up for the Import You will need to do a bit of prep work before you can do the actual import. But this setup work is trivial compared to trying…
Read More
24Mar 2014 by Charlie Nichols No Comments
Excel add-in update: version 1.6.3 of Excel PowerUps is Available – Get the update today

Excel add-in update: version 1.6.3 of Excel PowerUps is Available – Get the update today

100 level, Excel
A new version of the Excel add-in Excel PowerUps Premium Suite has been released. Like other releases, this Excel add-in version has a few new and enhanced functions along with some bug fixes and tool additions. Go to the download page. New Functions SKEW.P SKEW.P was added to match the Excel 2013 function of the same name. SHEET SHEET was added to match the Excel 2013 function of the same name. SHEETS SHEETS was added to match the Excel 2013 function of the same name. pwrISREGEXMATCH pwrISREGEXMATCH will identify a regular expression match within a string. pwrREGEXREPLACE pwrREGEXREPLACE will replace text identified by a regular expression with text provided in the function call. Enhanced Functions pwrISBROKENURL pwrISBROKENURL added identification of bad URLs by Title or URL pattern. Now you can…
Read More
23Mar 2014 by Charlie Nichols No Comments
Add Excel 2013 Functions to Excel 2010

Add Excel 2013 Functions to Excel 2010

200 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Get the Latest Excel Functions Working in Older Versions of Excel Do you have a number of Excel 2010, Excel 2007, or Excel 2003 licenses that you want or need to get another year or more of use from? Do you need to hold off on upgrading all of your licenses to Excel 2013, at least for now? But are you in need of some of the new functions that were added in Excel 2013? Perhaps you have a worksheet that was created in Excel 2013 that makes use of one or more of the new Excel functions and you need to be able to open and work with the same workbook on PCs with Excel 2010, 2007, or 2003. You can. Excel Forward Compatibility Adapter You can…
Read More