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.
Save time with our "Done For You" Excel products.
- Pivot Power Premium Add-in - Time-saving tools for pivot table users
- Data Entry Popup Kit - Quick and easy data entry with a popup list
- Data Entry Search Popup - Enhanced data entry popup, with Search box
- Excel Tools Add-in - Make instant backups, sheet list with links, much more
- More Excel products from Contextures
Note: I am an affiliate for the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2019
All rights reserved.