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.
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:
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.