October 13, 2015
Note: For some products mentioned below, I earn a commission on sales. That helps support the free tutorials on my site.
In this week's email question, Simon wanted to find the lowest price for a product, choosing from 3 different supplier.
"compare 3 excel price file from 3 different suppliers, all have a common field code...and return the lowest price and supplier"
I used the INDEX and MATCH functions to find the best price, and added conditional formatting, to highlight the lowest price in each row. You can click here to download the file, and please let me know if you've solved a similar problem, using a different technique.
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.
Then, instead of using those default values, you can use the CHOOSE function to assign a different value to each button. The CHOOSE function is a powerful and vastly underused tool in Excel.
Thanks for your nominations and I've listed all the nominees on my Spreadsheet Day blog. Next week you can vote for your favorite Spreadsheet program – our first category for the Hall of Fame.
Excel Dashboard Course: Mynda Treacy has opened registration for her acclaimed Excel Dashboard course, and you'll get 20% off, if you sign up by October 22nd. The course is a great investment, and you can read my review for the highlights, and more information.
Free Dashboard Webinars: Mynda is also offering two free one-hour webinars -- 1) How to Build Excel Dashboards, and 2) Dashboards with Power Query and Power Pivot. Get the details, and sign up for a date and time that is convenient for you.
Here are a couple of Excel articles I read recently, that you might find interesting and useful.
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. (Level - Basic)
Before You Work -- 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. (Level - Basic)
More Excel Articles -- Find links to more articles in the latest Excel Weekly Roundup on my Contextures blog. And for a bit of spreadsheet humour, you can see what people are saying about Excel, in my weekly collection of tweets.
Our family came for Thanksgiving dinner on Sunday, and we somehow managed to fit 16 people around the table. It was a tight fit, and very noisy, but lots of fun to get together. Of course there was turkey, and pumpkin pie, and I made the tomatoes look festive in a fancy dish. Now we can relax until Christmas, and I think that's at least 6 months from now. ;-)
NOTE: If you have any problems with the links in the email, you can see this newsletter on my website -- copy this link and paste it into your browser: https://www.contextures.com/newsletter/excelnews2015/20151013ctx.html
That's it for this week! If you have any comments or questions, send me an email.
[email protected] contextures.com
Last updated: December 15, 2019 2:30 PM