I have a requirement to rank items in a longer statement that includes the following if statement:
,IF(K13=" "," ",IF($Q$2=" ",RANK(K13,(INDIRECT("$K$10:$k13")),1),
it works wonderfully until I insert a row beneath it which results in the following:
,IF(K14=" "," ",IF($Q$2=" ",RANK(K14,(INDIRECT("$K$10:$k13")),1),
The problem I have is that the items to be ranked now span from K10 to K14. any ideas on how to solve this situation?
@ede6340 Why are you using Indirect? It doesn't make sense in this situation. Try referencing the cells like this, i.e. remove the Indirect()
When that is copied down to row 14, the reference will adjust.
If that doesn't do the trick, please attach a small data sample and explain what you would like to achieve.