Excel Worksheet Button Trick
February 26, 2019
What's in your workbook, worksheet button trick, and more, in this week's Excel news. Visit my Excel website for more tips, tutorials and videos, and check the index for past issues of this newsletter.
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
What's in your workbook? If you have to use an Excel file that someone else built, or a file that you created long ago, I've got a couple of macros that will help you find out what's hiding in there.
The first macro lists all the pivot tables in your workbook, with details on where they're located, and the source data, There's a link to each pivot table, so it's easy to find it, when you're troubleshooting.
The second macro lists all the defined names in your workbook. The list shows the name, its Refers To formula, the number of cells in the range, its location, and the scope (workbook or worksheet).
Download the sample files to get the macros, then add them to your own workbooks. Or, make them easy to run, by adding them your Custom Macro Workbook
If you have buttons or shapes on a worksheet, you can get the caption text from a worksheet cell, instead of typing the text in the shape. This is similar to the cereal box text in my "Switch Languages" blog post.
In this example, the button is linked to cell E2, which has a formula that checks the quantity:
=IF(C1<=40,"See price", "See discounted price")
Ctx Excel Tools - There's a new version of my Excel Tools add-in (1.91), with minor updates. Use your original download link to get the latest version, and email me if you need a new link.
Chart Tools - Jon Peltier released an update to his Excel Charting Utilities, so contact Jon if you need the new version.
Here are a couple of Excel articles that you might find useful or interesting.
VisiCalc - This video is for spreadsheet nerds only! Dan Bricklin did a VisiCalc demo for a college class recently, and focused on its interaction techniques. The VC demo is about 45 minutes, and he shows Excel too. See my notes about the video. (Level - All)
Decisions - Can Excel help you make decisions? This Decision Matrix Template is a Google Sheet, but after you open the sheet, click the File menu, then Download As, and choose the Excel option. The article explains how to use it, and there's an example sheet in the workbook. (Level - All)
Our granddaughter came to visit on the weekend and we tried a new art project - Altered Books. You start with an old book that nobody wants anymore, and turn it into a new book. Our book was called "How to Remodel Your Family Room", and now it's about Lighting and History.
To alter the book, you glue in pictures, or add paint, or other creative ideas. There are tutorials on the Altered Book Lover blog, if you'd like to try it. We're just getting started, and so far, it's lots of fun. And a nice break from spreadsheets!
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2019/20190226ctx.html
I'll also post any article updates or corrections there.
That's it for this week! If you have any comments or questions, send me an email.
Last updated: February 25, 2019 9:10 AM