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
<tbody>
</tbody>
B10-K10 & B11-K11
<tbody>
</tbody>
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>