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

11	90%	Blue Cricle
12	50%
13	65%
14	-10%	Red Circle
15	100%	Green Circle

<tbody>
</tbody>

B10-K10 & B11-K11

100%	90%	80%	75%	65%	50%	25%	10%	0%	-10%
Green Circle	Blue Circle	etc	etc	etc	Grey Circle	etc	etc	etc	Red Circle

<tbody>
</tbody>

 

[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.