Index Function

[Martel]

Hi. Can anyone please quickly provide an alternative formula to create a dynamic named range which will use the index function as opposed to offset or indirect. Sorry, can't get my head round this one.

I have :

[FONT=Verdana,Arial,Tahoma,Calibri,Geneva,sans-serif]=OFFSET(Demographics!$C$2,0,0,COUNTA(Demographics!$H:$H)-1,29)[/FONT]

This captures all used rows, using column H (-1 for headers) and as many columns as specified in last parameter (29)

I would like :

=demographics!$c$2:index..... etc etc. I am stuck at this point.

Thanks in advance. Martel

 

[Marcelo Branco]

Question
Column H contains numbers or texts?

M.

 

[Eric W]

I assume H contains text because Martel used COUNTA instead of COUNT. Given that, then maybe:

=Demographics!$C$2:INDEX(Demographics!$AE:$AE,LOOKUP("zzzzzzzzzz",Demographics!$H:$H,ROW(Demographics!$H:$H)))

I'm not sure why you want to get rid of OFFSET. If you're trying to avoid volatile functions, I don't think you'll see a significant performance increase. OFFSET is pretty fast.

 

[Marcelo Branco]

Maybe...

=Demographics!$C$2:INDEX(INDEX(Demographics!$C:$ZZ,0,29),COUNTA(Demographics!$H:$H)-1)

M.

 

[Martel]

Hi and thanks. Offset reportedly causing #Ref errors when deleting rows in dynamic range names. I have been getting this problem regularly so thought I would give index a go. Speed not important just looking for something that works reliably. I will give your suggestion a go and get back. Thanks again. Martel

 

[Martel]

To M , Eric et al. Plenty of responses to try for which many thanks to all. No early night for me...... ciao all