problems with an if statement using an indirect directive

%3CLINGO-SUB%20id%3D%22lingo-sub-1407132%22%20slang%3D%22en-US%22%3Eproblems%20with%20an%20if%20statement%20using%20an%20indirect%20directive%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407132%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20requirement%20to%20rank%20items%20in%20a%20longer%20statement%20that%20includes%20the%20following%20if%20statement%3A%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%2CIF(K13%3D%22%20%22%2C%22%20%22%2CIF(%24Q%242%3D%22%20%22%2CRANK(K13%2C(INDIRECT(%22%24K%2410%3A%24k13%22))%2C1)%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3Eit%20works%20wonderfully%20until%20I%20insert%20a%20row%20beneath%20it%20which%20results%20in%20the%20following%3A%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3E%2CIF(K14%3D%22%20%22%2C%22%20%22%2CIF(%24Q%242%3D%22%20%22%2CRANK(K14%2C(INDIRECT(%22%24K%2410%3A%24k13%22))%2C1)%2C%3C%2FFONT%3E%3C%2FP%3E%3CP%3E%3CFONT%20face%3D%22Calibri%22%20color%3D%22%23000000%22%3EThe%20problem%20I%20have%20is%20that%20the%20items%20to%20be%20ranked%20now%20span%20from%20K10%20to%20K14.%20any%20ideas%20on%20how%20to%20solve%20this%20situation%3F%3C%2FFONT%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1407132%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1407481%22%20slang%3D%22en-US%22%3ERe%3A%20problems%20with%20an%20if%20statement%20using%20an%20indirect%20directive%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1407481%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F675032%22%20target%3D%22_blank%22%3E%40ede6340%3C%2FA%3E%26nbsp%3BWhy%20are%20you%20using%20Indirect%3F%20It%20doesn't%20make%20sense%20in%20this%20situation.%20Try%20referencing%20the%20cells%20like%20this%2C%20i.e.%20remove%20the%20Indirect()%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3ERANK(K13%2C%24K%2410%3A%24k13%2C1)%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EWhen%20that%20is%20copied%20down%20to%20row%2014%2C%20the%20reference%20will%20adjust.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%3EIf%20that%20doesn't%20do%20the%20trick%2C%20please%20attach%20a%20small%20data%20sample%20and%20explain%20what%20you%20would%20like%20to%20achieve.%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
New Contributor

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?

1 Reply
Highlighted

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