How to show a picture in a cell depending on the v

Question

Say I have a two column table with "Weather Types". The first column has a weather type (eg rain, snow, ice, cloud, sun) and the second column has a picture icon that represents the weather type.

Say I have a second "Weekdays" table that shows the weather expected this week again with the same two columns, but this time with 7 rows ie for the 7 days. However, let's say the first column of this table is updated automatically every week, what if I want the picture displayed in the second column to automatically match the picture in the first table for the corresponding weather type.

Answer 1

Additional notes on setting picture sources via VBA.

It's possible to have an assortment of picture shapes (and/or text box shapes) on a hidden sheet, for example sheets("Images"), and then, via VBA, set a picture shape on your main sheet to any one of them.

Example
Sheets("Images") contains "pic1" in $A$1, "pic2" in B2, "txt1" (a shape of the text variety rather than a picture) in C3.
Sheets(1) contains a picture "picTest" (initially created by copying pic1 from the Images sheet).
Set the formula for picTest to =Images!SB$2 or =Images!C3 etc. and the image will change accordingly.

This can be achieved with VBA: sheets(1).pictures("picTest").formula = "Images!$A$1"

Beware it may not copy over any picture format attributes, so if some images have non-default formatting applied, you'll need to script the copying over of the relevant properties.

Answer 2

This sort of scenario can be solved fairly easily.

The most important things to realise is:

If you select any picture and then type a cell reference into the formula bar (eg =A1, or =A1:D1), that formula is used by the picture to determine what to display. The formula causes the picture frame to "look for" any shape objects that are positioned over the cell(s) referenced in the formula. The selected picture will then display the part of the shapes (ie pictures) that are over the cells in the formula reference.

This means that the shapes whose images are being looked up will usually need to be displayed fully within a single cell.

Capiche!?

So in the scenario I painted in my question, the "Weekdays" table would need a empty picture on every line, with a formula that RESULTED in a reference to a cell in the "weather types" table that has the weather picture "over" it. This gets trickier than you might expect as the empty picture does not "know" which cell is it over.

This means that every picture which is having the image it displays looked up needs to be linked to a cell. This is achieved by creating a named function or a named range for each picture that needs to have its picture looked up. This named function/range references a cell related to that single picture (I refer to this later as the RELATED_CELL)

There are a number of ways of using the named function to return a reference to a cell. Two examples are below:

EXAMPLE METHOD 1

This example relies on the RELATED_CELL containing part of the name of a named range. Then INDIRECT is used to get the reference of the cell referenced by the named range. This requires a named range for every individual cell in the column containing the WeatherTypeName that will used to lookup the pictures.

This technique is described very well in an article here.

EXAMPLE METHOD 2 (My preferred method)

In this example the RELATED_CELL contains the WeatherType name to be looked up. It does not rely on all the cells in the the weather types table being named ranges. Instead it uses a "lookup" method that scans and inspects the weather type name values to find the picture.

A full desciption can be found in a comment on the page here

The attachment provided by the commenter is very useful see here

Note that you can't use VLOOKUP itself because it returns the value of the cell rather than a reference to it. Instead MATCH, ADDRESS, and INDIRECT are used to lookup the value in the RELATED_CELL.

I want to add that I learnt these techniques from the links given and have mimicked the example in the pdf. I hope the points I explain above will help you more easily understand the approaches.

Harvey