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

[khill007]

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
[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]11[/TD]
[TD]90%[/TD]
[TD]Blue
Cricle[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]50%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]65%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]-10%[/TD]
[TD]Red
Circle[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]100%[/TD]
[TD]Green
Circle[/TD]
[/TR]
</tbody>[/TABLE]


B10-K10 & B11-K11
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]100%[/TD]
[TD]90%[/TD]
[TD]80%[/TD]
[TD]75%[/TD]
[TD]65%[/TD]
[TD]50%[/TD]
[TD]25%[/TD]
[TD]10%[/TD]
[TD]0%[/TD]
[TD]-10%[/TD]
[/TR]
[TR]
[TD] Green
Circle[/TD]
[TD]Blue
Circle[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]Grey
Circle[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]etc[/TD]
[TD]Red
Circle[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

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]

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.

 

[Marcelo Branco]

Sorry, I misunderstood what you are looking for.
Disregard my post above

M.