Excel Roundup 20151012
In this week’s roundup, listing comments, LEGO, option buttons, and inheriting an Excel workbook.
1. Adjust Option Button Scores
You can add option buttons to a worksheet, and link them to a cell. Then, when a button is clicked, its index number is automatically shown in the linked cell. Instead of using those default values, you can use the CHOOSE function to assign a different value to each button.
2. The NPV Function
On the Feasibility Pro blog, Naiyer Jawaid takes a look at the NPV function in Excel and explains why the result may differ from a manual calculation.
3. Excel + LEGO
At the recent Oz Comic Con, and artist used Excel to play the layout of blocks for his enormous LEGO artwork. It wasn’t quite finished, but still impressive!
4. Total Outstanding Invoices
On the TechRepublic blog, Susan Harkins shows 3 different ways to tackle a problem in Excel. She uses the Subtotal feature, pivot tables, and named tables, to sum invoice amounts that are 30 and 60 days outstanding.
5. Extract Worksheet Comments
Scott Lyerly shows how to extract all the comments from a worksheet, and put the text in another cell in the same row. The macro also gives you the option to delete the comments as you extract them, if you want to clean up the sheet.
If you’d rather list the comments on a different sheet, or number the comments and list them, there’s code on my website for that.
6. Spreadsheet Hall of Fame Update
Thanks for your nominations for my proposed Spreadsheet Hall of Fame. I’ve listed all the nominees on my Spreadsheet Day blog, and grouped them into categories. This Saturday (Oct. 17th), I’ll post a poll so you can vote for your favorite Spreadsheet Program – our first category for the Hall of Fame.
7. Check That File
If you receive an Excel file that was built by someone else, Matthew Kuo has a list of 8 things you should check, before you start working on the file.
8. Excel Humour
Finally, for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets. Here’s one of my favourite tweets from this week’s collection.