Pop-up images in Excel
Q: Our company sells more than 4,000 sporting good items, and to help analyze these items, we export a list from our accounting system into Excel. The list includes size, weight, reorder point, reorder quantity, cost markup percentage, pricing, and images. To make individual items easier to work with, my workbook has a dropdown list and several formulas that repeat the data for any given inventory item at the top of the worksheet (in row 1) based on the item I select in cell A1. For example, the screenshot below shows line item data in row 5 repeated in row 1, which makes it easier to focus on this single item to analyze its details. The problem is I want the item image to also be repeated on row 1 (because the picture helps me differentiate between similar items), but I don’t know if this can be done.
Specifically, when the inventory item “Spalding Fielder’s Glove” (shown in row 5 in this example) is selected from the dropdown list in cell A1, I want the picture image of the glove to also appear in cell I1. Is this possible, and if so, how?
A: The solution you seek is indeed possible, but be forewarned that the procedures are advanced, so don your thinking cap. In essence, you want to create a new formula that is virtually identical to the ones you have already created in row 1, but instead of entering this new formula into cell I1, you need to enter it into Excel’s Name Manager, where the formula can be assigned a specific name. Next, you want to insert an image into cell I1, and then tag that image with the new formula created in Name Manager. The following steps explain this process in detail.
1. Copy your formula to cell I1. You’ve already done a good job of creating the proper formulas (using the INDEX and MATCH functions as pictured in the screenshot near the top of the next column) in cells B1 through H1 to repeat an inventory item’s data on row 1. Next, copy the formula you have already created in cell H1 and paste it to cell I1, then edit the formula to make all of the cell references absolute, by inserting dollar signs where needed. The result in cell I1 should appear as follows:
Note: The resulting formula in cell I1 will produce a zero result. This is normal and expected. (The reason for copying and pasting this formula into cell I1 is to more easily create the final formula with the proper cell references that you will need to paste into Name Manager in step 3 below. Eventually, you will delete this formula from cell I1, in step 4 below.)
2. Copy the formula text from cell I1. Select cell I1 and press the F2 key to enter Edit Mode. Select (or highlight) the formula text and press Ctrl+C to copy the formula text, then press ESC to exit Edit Mode.
3. Create a new formula in Name Manager. From the Formulas tab, select Name Manager, and in the Name Manager dialog box, click New. (Tip: At this point it will be helpful to drag the New Name dialog box wider so you can better see the formula you are creating.) In the New Name dialog box enter the phrase itempicture in the Name box (see screenshot below), paste the formula you copied from cell I1 in the previous step into the Refers to box, and then click OK.
4. Clean up. Make row 1 taller so the resulting item image fits. Also, erase the formula you previously entered into cell I1 because it is no longer needed.
5. Insert picture and affix a tag. Insert (or copy and paste) any picture into cell I1 and then resize and reposition the picture so it fits within the borders of cell I1. Click on the picture in cell I1 to select it, then in Excel’s Formula Bar enter the phrase =itempicture and press the Enter key. (This process will affix a tag to the image linking it to the new formula created using Name Manager.)
Thereafter, the image in cell I1 will change to reflect the inventory item selected in cell A1. This particular application of Name Manager may seem foreign to you, but once created, its use is simple and the results are compelling. You can download an Excel workbook containing this solution at carltoncollins.com/picture.xlsx.
