Variable Shape lookup using defined name - works only with anchored cells in match function

khill007

New Member
Joined
Mar 3, 2017
Messages
2
I have an Excel problem I cannot seem to solve. I am trying to do a dynamic shape/color change based on a percentage, the index of shapes and matching percentages are in B10:K10 and B11:K11.

Defined Names
- Status =INDEX(Sheet1!$B$11:$K$11,MATCH(Sheet1!$M$11,Sheet1!$B$10:$K$10,0)) With the anchored column and row locations this will work, changing the 90% # will change the shape color.
- Status2 =INDEX(Sheet1!$B$11:$K$11,MATCH(Sheet1!$M11,Sheet1!$B$10:$K$10,0)) With a relative row definition it will not work, it selects the wrong shape/color.

I thought I could just copy the shape down the list and as the percentages changed the color of the shape would change. I have researched and tried everything I could think of including just putting the formula on the shape rather than the defined name but this gives a range reference or defined name error. I’m sure it is probably something simple, any thoughts?

Sorry I could not figure out how to get the images to load.
Columns
row M N
1190%Blue
Cricle
1250%
1365%
14-10%Red
Circle
15100%Green
Circle

<tbody>
</tbody>


B10-K10 & B11-K11
100%90%80%75%65%50%25%10%0%-10%
Green
Circle
Blue
Circle
etcetcetcGrey
Circle
etcetcetcRed
Circle

<tbody>
</tbody>
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,317
Welcome to Mr Excel

As you are using a relative reference (row) to define Status2 you must select a cell in the proper row to create this named range,

Try this
Select N11
Formulas > Names Manager > New
Name: Status2
Refers to: =INDEX(Sheet1!$B$11:$K$11,MATCH(Sheet1!$M11,Sheet1!$B$10:$K$10,0))

Then you can put in N11
=Status2
and copy down

Hope this helps

M.
 

khill007

New Member
Joined
Mar 3, 2017
Messages
2
Sorry, I must be missing something, I think what you said is what I am doing. Here are the steps

1. I insert a picture image into cell N11 (I read you had to put a picture image not a shape image)
2. I click on the picture object and in the formula space put =Status2.....This does not render the proper shape/color
3. For option 2 above if I put in =Status in column N11 the proper shape/color is rendered, changing the percentage in column M will change the shape/color i.e. works great
4. If I copy the contents of cell N11 down to N12 the contents will render based on the anchored reference $M$11. Change the value in M11 and both shapes/colors changes as expected.

It does not work if I remove the anchoring of column in the match function.
 

Forum statistics

Threads
1,082,259
Messages
5,364,098
Members
400,779
Latest member
lumers

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top