In Excel, you can use the SUMIF and COUNTIF functions, to sum and count values, based on criteria. Did you know that you can also calculate an Excel average, based on multiple criteria?
Create Amazon Affiliate Links in Excel
This week, Dick Kusleika posted his Amazon Linkerator – an Excel file lets you create links to Amazon products.
First, you find a product on Amazon, and copy its web page URL. Then, open the form, enter a product code and description, and it creates a link for you.
It’s very fancy, and you can download the sample file, to try it for yourself. It uses an Excel UserForm, and you can modify the code to add your own information.
Jimmy Pena has an Amazon Link Builder too, and you can see the details here: Amazon Link Builder
Low Tech Amazon Link Builder
I build Amazon links too, and you can see lots of them on my Excel Book List page.
To create my links, I use worksheet formulas, instead of a fancy UserForm. If you like things simple, you can try this method.
You enter the product code and product title, and then copy the link or the HTML code, and paste it into your blog post or web page.
WARNING: Check the latest information on the Amazon website, to be sure that these short links are still permitted. Their policies can change at any time.
How It Works
First, a link is created in cell B5, from the Amazon URL, the product code (ASIN) and the Tracking ID.
=https://amzn.com/ & ProdCode & “?tag=” &TrackID
Note: Cell B3 is formatted as Text, because some codes start with a zero, and you don’t want Excel to remove those.
Then, that link is used in the to create the HTML code in cell B7.
The four HTML cells have snippets of text that are required for building the HTML code. The formula in cell B7 combines the product title and the link, with four snippets of text.
=HTML_01 & ProdTitle & HTML_02 & ProdLink
& HTML_03 & ProdTitle & HTML_04
Download the Sample File
To test the Amazon link formulas, you can download my sample file, from the Excel Sample Files page on my Contextures website. In the Functions section, look for FN0025 – Build Amazon Affiliate Links
____________________
Validate Entries in Excel Combo Box
One of my Excel sample files lets you double-click on a cell that has a data validation list, and a combo box pops up. The combo box has advantages, because you can set the font size, and the number of rows in the drop down list, and the entry autocompletes as you type.
Excel UserForm with Dependent Combo Boxes
In an Excel UserForm, you can add combo boxes to make data entry easier.
The UserForm in this example is used to enter parts information onto a hidden inventory sheet.
Before selecting a Part ID, you have to select a Part Type. When the Part Type combo box is updated, code runs, and creates a list of parts, for the selected part type.
How It Works
In the background, an Advanced Filter runs, to create a worksheet list of parts for the selected part type.
That list is set up as a named range, and the range name is used as the Part ID combo box row source.
Watch the Video
To see how the dependent combo boxes work, watch this short video.
Video Timeline
- 0:00 UserForm Demo
- 0:53 Lookup Lists Sheet
- 1:25 See How Macro Works
- 1:48 Named Range for Parts List
- 2:02 UserForm Macro Code
Download the Sample File
To test the dependent combo boxes on the UserForm, and to see the code, go to my Contextures website, and download the sample file.
_______________
Click to Show or Hide Excel Chart Data
If you’re building Excel reports for other people to use, you can add a few interactive chart features, to let people customize the reports.
In this example, there is a check box beside each region name, in the sales summary table.
Update Pivot Table With Scroll Bar
A couple of months ago, I shared an example with a scroll bar that selects the dates for an Excel report. There is a pivot table on a hidden sheet, and a summary report uses GetPivotData formulas to pull data from that pivot table.
Sum For a Date Range in Excel
First, some news about the upcoming Office 365 launch, and then a tip on how to sum for a date range in Excel.
Show Data Validation Message in Text Box
When you’re setting up data validation on a worksheet, you can include an Input Message, to help anyone who’s using the workbook.
You’ll have to get to the point quickly though – the message is limited to 255 characters.
There are other limitations too – you can’t control the size of the text box, and you can’t change its font size or fill colour, unless you change your Windows settings.
Show Input Message in a Text Box
As an alternative to the Input Message popup, you can show a message in a text box, at the top of the worksheet. In the text box, you can set the font type, font size, font colour, and fill colour, to suit your worksheet.
The Input Message display is turned off, but the Title and Message are entered in the data validation window.
There is a sample file on my website that you can download, and instructions for this technique on my Contextures website: Display Input Messages in a Text Box
Show Longer Messages
The original sample file uses the input message text, which is limited to 255 characters. In an email, Richard G. asked about showing a longer message in the text box.
So, I’ve created a new sample file, that uses most of the code from the original example, and adds a new feature. Now, you can create a list of Input Message Titles, and the message that you want to display for those titles.
Then, when you click on a data validation cell, its title is used as a lookup in the messages table. If there is an entry for that title, the Additional Message text is added to the end of any existing Input Message text. Then, the entire text string is shown in the text box at the top of the worksheet.
Download the Sample File
To download either the original sample workbook, or the Longer Message sample file, please visit my Contextures website: Display Input Messages in a Text Box
The files are zipped, and they contain macros, so remember to enable macros when testing the files.
___________________
List All Pivot Table Formulas
A few months ago, I shared my code for listing all the formulas in an Excel workbook. The code creates a new worksheet, with details on each formula’s worksheet name, cell address, the formula and the formula in R1C1 format.
Round to a Nickel in Excel
If you’ve been following the Canadian news (and who isn’t?), you know that the penny has been eliminated from circulation. To honour the occasion, Google made a special doodle for google.ca on February 4, 2013.
Rounding Guidelines
If you’re shopping with cash now, the final amount will be rounded up or down, to the nearest nickel. There are guidelines posted on the Royal Canadian Mint’s website: Eliminating the Penny: Rounding [article no longer available]
Rounding to the Nearest Cent
As an example, the Mint’s website shows the purchase of coffee and a sandwich, with tax, for a grand total of $4.86.
The tax department says to round the tax to the nearest cent, so you can use Excel’s ROUND function for to calculate the HST. Just multiply the subtotal by the tax rate, and round to 2 decimal places. Here is the formula in cell B6:
=ROUND(B5*D6,2)
Rounding to the Nearest Nickel
With the HST, the grand total for the lunch is $4.86. We don’t have pennies now, so the cash payment will be rounded to the nearest nickel. Excel’s ROUND function can’t help with that.
Fortunately, there is another rounding function – MROUND – that can round to a specified amount.
- The MROUND function has two arguments – the number, and the multiple.
In this example, we want to round the grand total, which is in cell B7. We’ll enter the multiple in cell B9, to show how the cash payment was rounded.
Here is the cash payment rounding formula in cell B10:
=MROUND($B$7,$B$9)
- Note: For Excel 2003 and older, the MROUND function is available after you load the Analysis Toolpak add-in.
Test the MROUND Formula
A nickel is worth 5 cents, so what happens if you enter a 5 in cell B9, to use as the multiple?
Oops! That rounds the amount to 5 dollars, instead of the nearest nickel.
Change the amount in cell B9 to 0.05, which is the way that you’d enter a nickel amount in a worksheet.
Perfect! With the MROUND function, and a multiple of 0.05, you can round those sales totals to the nearest nickel.
____________________
Round to a Nickel in Excel
________________