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
15Feb 2014 by Charlie Nichols No Comments
Creating a Constant in Excel

Creating a Constant in Excel

100 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Occasionally, it's useful to have a constant defined that you can simply refer to instead of having to remember the value. For example, the value of Pi is commonly remembered as 3.14. Or, you may need to have a value that may change but you don't want to update formulas throughout your workbook whenever the value changes. One way to go about this is by creating a constant in Excel. Constants have the properties desired as well as showing up in the autocomplete list that appears when you enter formulas. Steps for Creating a Constant in Excel Creating a constant in Excel is pretty simple. The steps are outlined below. First, go to the Formulas tab in Excel and click Define Name on the Define Name menu in…
Read More
14Feb 2014 by Charlie Nichols No Comments
Excel Order of Operations is the Same as Math

Excel Order of Operations is the Same as Math

100 level, Excel
[wp_ad_camp_1] [wp_ad_camp_4] Excel follows the same order of operations that you learned back in your math classes. If you don't pay attention to these rules when you create your formulas, you may wind up struggling to figure out why your results make no sense. Excel Order of Operations The following ordering dictates in which order your formulas will get evaluated. Although our eyes may scan from left to right, Excel doesn't look at formulas that way. ParenthesesExponentsMultiplication and DivisionAddition and Subtraction Excel Order of Operations Example To illustrate how the order of operations matters, consider the example below. 1+2*4 If you were to try and evaluate this from left to right as it is written, you would first get 1+2=3 Then, you would multiply that 3 times 4, giving you…
Read More