range not working. 1

[Chrissirhc]

My range sheetsToHide is equal to:

=OFFSET(Control!$B$54,0,0,MATCH("*",Control!$B:$B,-1),1)

This range is therefore meant to start at $B$54 and end when there is no more text in that column. However instead it ends about 30 rows below the last entry of text in the B column.

NB: There are other bits of text in the B column.

Any ideas why?

Thanks,

Chris

 

[GlennUK]

Yes, I've an idea why, it looks as if it's because that's what you've asked for in your formula. This should do it:

=OFFSET(Control!$B$54,0,0,MATCH("*",Control!$B:$B,-1)-53,1)

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Chrissirhc]

Thanks it worked, however I have two questions:

1) Can you explain how you worked that out?
2) Do you think using dynamic ranges is bad?

Thanks,

Chris

 

[GlennUK]

1) Your range started on row 54 ... so finding the last entry ( with the Match ), gives a row number 53 too large to use in the Offset, so I adjusted it.

2) I use dynamic ranges a lot, BUT, if I had a range I need to reference and the definition is not going to change I won't use a dynamic range just for the sake of it, I'll used a fixed named range instead. So, no, using dynamic ranges isn't bad, but I only use them when necessary.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.