3

The intent is to create a sheet that references orders from customers with an image of each item ordered from my catalog of inventory.

I need to create a spreadsheet that references item SKUs to display an image of a unique item. For example: 004 entered in cell A:1 should lookup 004 on sheet "images" and return the value in column 2 that matches 004. I have successfully created this formula using the VLOOKUP function.

However, I want Numbers to populate the image in the resulting cell. The way my formula currently works it will only return the text in the cell. How do I get it to return the image value? If I delete the text out of the cell corresponding to 004 the result returned is 0 even though there is an image pasted into the cell.

Current formula:

VLOOKUP($A2,images::Table 1::A$1:B$10,2,FALSE)

I had dragged images from the Finder into cells but it appears as though they only show up as background images in the cells or something like that.

Test sheet with Formula Test inventory image sheet

2 Answers 2

1
  • The images aren't the actual content of the cell, they're just cell formatting (background image), and formula cannot interact with formatting, only with cell contents.
  • Conditional formatting can only apply a restricted set of formatting options and unfortunately images are not one of these.
  • AppleScript does not provide the ability to set cell background images, only cell background colours (even though shapes and text items do have the ability for background images).

Therefore currently this is not possible.

2
  • Thank you for the quick response. I was afraid of that. I'm not very familiar with AppleScript but I was wondering then if there would be a way to use AppleScript to do what I am looking for. Any idea? Nov 3, 2017 at 20:21
  • @Jeremy Unfortunately it's not possible to set cell background images with AppleScript, see edit
    – grg
    Nov 3, 2017 at 20:32
0

Here's a hacky way to get this done:

  • Take your images table and overlay it on the test sheet.
  • Enlarge the row widths so they are all the same.
  • Add a formula to the images table that checks the test sheet if the SKU is present. This could be in a separate hidden cell or in the image cell itself with transparent text: Column Match Formula
  • Filter the image table for True results enter image description here
  • Hide all cells besides the Column B, (or use transparent results)

Keep in mind that Test Sheet row A has to be sorted ascending for this to work with no duplicates. Alternatively, you can have multiple horizontal tables of overlapping rows one to test each SKU for a match. Finally, if you're at liberty to use cardinal numbers for SKUs you can substitute empty Row() or Column() to stand in for SKU references.
enter image description here

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .