Display image using Name Manager on a dynamic spilled range

adi772

New Member
Joined
Nov 30, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hi all. I am new here, so apologies if I miss any conventions with making this post. I'm using Office 365, on Windows.

Here is a link to a screen-recording of the spreadsheet/dashboard. (Unfortunately the screen-recording does not capture the drop-down list, for some reason, but you can clearly see that I have clicked the arrow and selected an option.)

I am making a 'dashboard' of sorts, for a database I have constructed. The database contains 130+ rows, with each representing a chemical compound. The compounds are named with a numerical code and a letter (e.g. 864.3a), and each of them is accompanied by an image representing the chemical structure, in an adjacent column. Currently, I have used data validation to generate some drop-down boxes, which I have coded to filter the 130+ compounds into a dynamic list. This list appears on the right-hand side of the screen, and is a spilled range. The same list appears as options in the drop-down box under "Compound" (not captured in the screen-recording); I have used Data Validation for this drop-down and have used the spilled range as the source. Once I select an option in the drop-down from this spilled range, the corresponding image appears next to it. I have essentially achieved this by following the steps in this video.

What I want to do now is have the corresponding images appear alongside the dynamic spilled range, which of course changes depending on the parameter(s) selected in the drop-down (grey box). I have tried using Name Manager in conjunction with "=INDIRECT" as per the video above, which works in the first instance. But it seems that this method doesn't work with spilled ranges.

I would greatly appreciate any help! Thank you kindly for reading and thank you in advance for any suggestions or solutions.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,734
It works fine with spilled ranges, but it is some work as you will have to create a separate named formula for each of the images placed next to the spill range

1607006406884.png
 

adi772

New Member
Joined
Nov 30, 2020
Messages
2
Office Version
  1. 365
Platform
  1. Windows
It works fine with spilled ranges, but it is some work as you will have to create a separate named formula for each of the images placed next to the spill range
Thank you very much for your reply. I'm not sure I understand, though. In your example, for column E, do I use the "INDIRECT" function to refer to the each cell of the spill in column D? I have tried just referencing the spill, rather than each cell individually.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,611
Messages
5,625,829
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top