May 20 2020 05:16 PM
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?
May 20 2020 10:09 PM - edited May 20 2020 10:10 PM
@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()
RANK(K13,$K$10:$k13,1)
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.