Contextures

Contextures News 20190226

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.

Workbook Info

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.

pivot table details list

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).

Name details list

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

Button Trick

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")

  • Click on the button to select it
  • Click in the Formula Bar, and type an equal sign =
  • Click on the cell with the text for the button, and press Enter

button linked to cell

Product Updates

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.

  • 2 new commands for data validation documentation lists - DV with Names, and DV with Details
  • new command to change formatted numbers to text, keeping leading zeros, dashes, etc.

Chart Tools - Jon Peltier released an update to his Excel Charting Utilities, so contact Jon if you need the new version.

Excel Articles

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)

Also see: My Excel Products || Excel Events || Previous Issues || Weekly Humour

Altered Book

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!

weekly photo

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.

Debra Dalgleish
dsdalg@ gmail.com

Debra Dalgleish

P.S. You can choose the full Contextures news package (news and occasional special announcements), or basic news package (news only). Click either link to change your option.

 

 

Get weekly Excel tips from Debra

 

pivot power free

 

 

 

 

 

Last updated: February 25, 2019 9:10 AM