Search Contextures Sites
Excel Weekly News from Contextures Nov 6, 2012
Show interactive drop lines in a chart, create pivot table copies automatically, and other tips, in this week's Excel news from Contextures.
- Debra firstname.lastname@example.org
Allow Other Entries With Excel Drop Down List
If you add drop down lists in a workbook, it's easy for people to enter data, by selecting items from the list. In some cases though, you might want to allow other entries in those cells, in addition to the items in the drop down list.
To allow other entries, you can turn off the Error Alerts in the data validation cells.
To see the steps for changing this setting, please click here: Allow Other Entries With Excel Drop Down List
Create Worksheet for Each Pivot Item
With a built-in feature in Excel, you can automatically create copies of a pivot table, on new worksheets, named for items in a pivot table's Report Filter.
If you want to copy all the other content from the original worksheet, along with the pivot table, you can use the sample code on my Contextures site on my Contextures site. My code adds a "PT_" prefix to the sheets, and deletes any existing "PT_" sheets, if necessary.
You can read the details, and find a link to my sample code, here: Create Worksheet for Each Pivot Item
Find Email Address With Index and Match
My daughter needed some help with Excel, while planning events for Movember Canada. We created and INDEX and MATCH formula that found the city, based on a person's email address in a master list.
See how we solved the Excel problem, and learn a bit about Movember: Excel and Movember Moustaches
Programmers Want Less Stupid And More Programming
Here is another thought-provoking article from Andrew Wulf, in which he talks about motivating programmers. What makes you work harder, and stay loyal to a company? Does this sound like you:
"the real motivator is writing great code, building cool products, and solving nasty problems."
Here is the link to the article: What Programmers Want Is Less Stupid And More Programming
Interactive Drop Lines on Excel Charts
On his Clearly and Simply blog, Robert Mundigl shows us how to add interactive drop lines in an Excel chart. Click on a data point, and lines connect that point to one or two of the axes.
This technique requires programming, and there are examples for line, bar, scatter and column charts.
See the details here, and download a sample file with the charts and VBA code:
Recommended Product: Excel Dashboard Course
Remember, you only have a few more days to register for the online Excel Dashboards Course, offered by Mynda Treacy at My Online Training Hub.
This comprehensive Excel Dashboard Course will teach you the simple techniques you can apply in Excel to make killer dashboards that will set your skill level apart from the crowd.
Sign up now, to save 20%. This early-bird discount ends tomorrow, Nov. 7th, at 8 PM Pacific time, and registration closes on November 13th.
You can see the course details and a sample video here: Excel Dashboards Course
I highly recommend this training, to improve your dashboard skills. Go at your own pace -- you'll have 12 months of access to the videos and sample files. Or, go for one of the options that includes downloadable videos, so you can watch them offline, at any time.
Recommended Excel ToolsThey aren't free, but these Excel tools are a worthwhile investment, so please click on the links to take a look at their features, and decide if they're right for you.
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
- Excel Pivot Table Intro Course
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2014
All rights reserved.