Annoying Excel Problem
December 4, 2018
Show an update warning, fix an annoying problem, 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.
Here's a quick fix for an annoying Excel problem. If you're building a formula, and click on a pivot table value, Excel might insert a GetPivotData formula, instead of a cell reference. Then, when you copy that formula down, every row shows the same result. For example:
I love the GetPivotData function - it's a great way to pull summarized data without complex formulas. But, if you don't need it, here's now to turn it off, so you get normal cell references:
NOTE: This is a global setting for Excel, not just for the active workbook.
One of my Excel sample files has a data entry sheet, with buttons to add/update database records that are stored on a different sheet (away from the fumbly fingers of your co-workers!)
Someone asked how to remind people to save their changes, before wandering off (to a different sheet or record). That's an interesting question! How would you get people to save their changes?
My first thought was a macro -- Excel can run code when an "event" occurs, such as moving to a different sheet, or closing a file. But co-workers are tricky! No matter how many events we tried to catch, Joe in Accounting would do something unexpected.
So, my solution uses formulas and conditional formatting, to highlight any changes, and show an warning on the worksheet. If someone made changes, we'll remind them to save, but we won't track their every move, or nag them to save. That could slow down the workbook, and annoy everyone who uses it.
Off to the right, there are INDEX/MATCH formulas, to pull the record's current data from the database. The labels have conditional formatting that shows orange if the new value is different from the stored value.
Also, a message appears below the buttons, if anything has changed, with a bright yellow background. What do you think -- would that help your co-workers remember to save?
To get a copy of the workbook, go to the Data Entry and Update Form page, and click on Download #6 (With Alerts). There are notes in the file, to explain how the alerts work.
Quick Pick List: Last week, I showed you a Quick Pick list, and thanks for letting me know that you can also use Alt+Down Arrow to open the list. I made a short video to show how that works.
Advent Calendar: There was a no-macros Advent calendar in last week's news, and I've made another version, with fancier pictures. Get both versions on my Excel Samples page, in the Conditional Formatting section. They're files CF0011 and CF0010.
Here are a few Excel articles that you might find useful or interesting.
Charts - Philip Treacy created a free Excel add-in that lists all the charts in a workbook, and lets you export them as images. Or, to export items from a specific sheet, try Andy Pope's graphics exporter. It shows a preview of the selected item, and works with shapes, charts, Slicers, and more. (Level - All)
Last month I got a new iPhone, and when I take a photo, it also creates a little video file. Here's an animated gif, to show an example. My granddaughter had never seen a Bissell sweeper before, and was amazed that something so "low tech" actually worked!
Finally, Google showed me how to fix the photo problem. Open the Camera app, and at the top, tap the icon that looks like a yellow target. That icon turns the Live Photo feature on (yellow) or off (white).
NOTE: To read this newsletter online, paste this URL into your web browser: https://www.contextures.com/newsletter/excelnews2018/20181204ctx.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: December 3, 2018 9:47 AM