Protected Pivot Tables and Name Fixes
March 28, 2017
Free Data Analysis webinars tomorrow (March 29th), and more, in this week's Excel news. Visit my Excel website for many more tips, tutorials and videos -- let me know if you're looking for something and can't find it there.
Last week's challenge was to clean up a simple list of names, using Excel formulas. Thanks for sending your solutions, and as always, it was fun to see how you solved the problem.
To see the different solutions, including some very interesting approaches, download the completed Name Case Solution workbook. The zipped file is in xlsx format, and does not contain any macros.
If you protect a worksheet that has a pivot table, you can still use it, but with several limitations. There's a video on my website that shows what to do when you're protecting the sheet, and there is a list of the key limitations.
For example, you can't refresh a pivot table if its sheet is protected. And, if other pivot tables are based on the same pivot cache, you won't be able to refresh them either!
To get around that problem, I've written a couple of macros that temporarily unprotect the sheet, refresh, and turn on the protection again. Download the sample file that has test pivot tables, and the macros.
Here are a couple of recent Excel articles that you might find useful.
Data Analysis - Tomorrow, March 29th, register to watch the 5 free online sessions in the Business Analytics Marathon, from PASS. The sessions will focus on Data Driven Storytelling and Data Discovery. The sessions run from 17:00 to 21:00 GMT, and you can click the iCal link to add to Outlook and convert that to your own time zone. (Level - Intermediate/Advanced)
Programming - Mike Alexander explains how he organizes his VBA code in Excel, and one of the comments is from RubberDuckVBA -- a free, open source add-in for VBA, which looks interesting. (Level - Intermediate/Advanced)
Pivot Power Premium: There was a minor update to my Pivot Power Premium (PPP) add-in. Three new settings were added to the Set Prefs form -- Remove "Sum Of" Headings, Show Pivot Field List, and AutoFilter Date Grouping On/Off.
If you've purchased this add-in, you can download the latest version with your existing download link. If you need a new link, please email me, and I'll send you one.
Spring has sprung, but the trees are still a dreary brown -- no leaves or buds yet. This Sycamore stands out from the rest though, with its seed balls that look like decorations. I hope things start blooming soon!
That's it for this week! If there are topics that you'd like to see covered in future emails,
please let me know.
ddalgleish @ contextures.com
Note: I am an affiliate for some of the products mentioned in this email, and earn a commission on the sales.
Last updated: July 21, 2017 9:38 AM