Dynamic range not working [SOLVED]

[6tel]

Hello.


I am unsuccessfully trying to set a dynamic range at the "C" column of the "SUB" worksheet in the document attached:


https://dl.dropboxusercontent.com/u/43577856/20130819_indicadores_part_chandoo.xlsx


This is the formula I used:


=SUB!$C$3:INDEX(SUB!$C:$C;MATCH(REPT("z";255);SUB!$C:$C))


When used, it is supposed to detect the range up to the 622 row, but then, when checking the range, it keeps itself to the third row and it shouldn't. Why is that?


Can someone please tell me what's wrong and how to fix it quickly?

 

[NARAYANK991]

Hi ,


The problem is that column C contains numbers ; replace the :


REPT("z",255)


in the named range definition by :


999999999999999


Narayan

 

[6tel]

Thanks, Narayan... Ok, sorry, but I still don't get it... I tried these, but none is still working:


=SUB!$C$3:INDEX(SUB!$C:$C;MATCH(REP("z";999999999999);SUB!$C:$C))


=SUB!$C$3:INDEX(SUB!$C:$C;MATCH(999999999999);SUB!$C:$C)


=SUB!$C$3:INDEX(SUB!$C:$C;SUB!$C999999999999;SUB!$C:$C)


Excel says arguments are insufficient.


Could you post the right syntaxis? So sorry to bother.

 

[6tel]

Tried this too, but didn't work:


=SUB!$C$3:INDEX(SUB!$C:$C;999999999999;SUB!$C:$C)

 

[6tel]

Guess I found it:


=SUB!$C$3:INDEX(SUB!$C:$C;MATCH(999999999999;SUB!$C:$C))

 

[SirJB7]

Hi, 6tel!

Thanks for sharing your solution with the community, so as people who read this would have the issue or question and the solution or answer as well. Welcome back whenever needed or wanted.

Regards!

PS: