Display a graphic based on a cell value per row

Topic

New Reply

I have a spreadsheet that lists all rivers in the area and any one time we wnt to know the Flood warning level about all rivers. Each flood warning has its own icon. We would like to use these icons to be displayed in the sheet.

eg.

[TABLE=”class: grid”]
[TR]
[TD]River[/TD]
[TD]Flood Warning[/TD]
[TD]Icon[/TD]
[/TR]
[TR]
[TD]Thames[/TD]
[TD]None[/TD]
[TD]None icon[/TD]
[/TR]
[TR]
[TD]Leam[/TD]
[TD]Severe[/TD]
[TD]Severe Icon[/TD]
[/TR]
[/TABLE]

Is this possible? Most stuff i’ve see only seems to hide pictures until the info in a cell is entered but I would want them all displayed.

Thanks

[TABLE=”class: grid”]
[TR]
[TD]River[/TD]
[TD]Flood Warning[/TD]
[TD]Icon[/TD]
[/TR]
[TR]
[TD]Thames[/TD]
[TD]None[/TD]
[TD]None icon[/TD]
[/TR]
[TR]
[TD]Leam[/TD]
[TD]Severe[/TD]
[TD]Severe Icon[/TD]
[/TR]
[/TABLE]

Reply | Quote

Replies

Specifically, what “icons” are you trying to use? Can you post a manually generated example???

Reply | Quote

They are specific flood warning symbols which unfortuantley I don’t have (as usual I have been asked by a 3rd party with very little info) but they are jpegs. Apologies that the table I created above didn’t work.

I suggested basic condititional formatting would help them flag the information but thats not enough, they want the pretty symbols by each river

31877-rivers

Reply | Quote

Does the attached do what you want?

The SYMBOLS sheet has pics and each are located in a cell which is a named range (“low”, “high” severe”)

The other sheet has the names of the rivers in Col A. In Col B, data validation is used to choose one of the three warnings (from the list in SYMBOLS). The graphics in column C are each linked to a named formula which is named for the river in Col A. The named formulas each use INDIRECT to refer to the warning in Col B. So when the text in col B is changed, the graphic in Col C is changed as well.

Steve

Reply | Quote

I think so, thanks.

I can see what you have done but not how you have done it. Thanks.

Reply | Quote

This is great – is it possible to display images that are separate .jpg files? I want to create a file with photos of students interviewing with different companies. There are several hundred students and it would be easier (and have a much smaller file) if the final spreadsheet did not contains all the photos.

Thanks.

Larry

Reply | Quote

It is done with range names. I thought my descriptions were relatively complete. Do you need clarification on anything in particular?

Steve

Reply | Quote

It is done with range names . . . need clarification?

Steve,
Been looking at your solution. Am impressed. I think I understand some of your explanation. but where do you decide which pic to display?
Conditional formating, or so?

Thanks
Leon

Reply | Quote

You can link photos but they will display as hyperlinks. When pressed the hyperlinks will open the default program for viewing the file, it will not display in excel. If you want them to display in Excel, I think you will have to embed them. You could shrink the photos to close to the display size and i think that will make the excel file smaller.

Steve

Reply | Quote

It is not conditional formatting. It is named formulas (aka named ranges).

Look at sheet 1 for example. You want to change the warning level for the Thames (row 2). To change the level, you go to B2 and enter a value (type or from the datavalidation list). For this example we will enter “high”.

If you select the picture placed in C2 you will see that the formula bar indicates “=Thames”. “Thames” is a named formula.

If you go to Formulas – Name manager and select the name “Thames” you will see it refers to:
=INDIRECT(Sheet1!$B$2)

This is an indirect reference to whatever is in Sheet1B2. In that cell we just entered in “high”. Therefore the reference in the named formula “Thames” is =Indirect(“high”). So we need to know now, what is the named formula “high”

If you go to Formulas – Name manager and select the name “high” you will see it refers to:
=Symbols!$B$3

So what is displayed in the graphic is whatever is in the Symbols sheet in Cell B3, which is the graphic for “high” warning.

The setup for this type of work requires a named formula for each of the warning levels (low, high, severe) each one referring to the range (which in this example is only 1 cell) that contains the appropriate graphic. Each river graphic also needs a named formula (I named them for the river, the name does not matter) which refers to a formula using INDIRECT which points to the location of where you enter the warning level (in these examples col B of the same row). If you add more rivers you will need to add more named formulas and more graphic and refer to the names.

Better?

Steve

Reply | Quote