indirect - dynamic range

[methody]

I have a base sheet. In a particular cell (B1) I have a data validaton list from which I can select the name of 4 other sheets. In each of these sheets the key range is C6 through to C26. When I select the sheet name from B1 I want the data validation cell in B4 to be populated by the appropriate cells in the range C6 to C26. I am able to do this but if only the cells C6 to C8 are filled I only want them to appear in the data validation cell.
I presume it is a combination of INDIRECT and DYNAMIC ranges using offset but I can't get it to work.

 

[Domenic]

Original post here...

 

[GlennUK]

Have a look here, and see if it helps:

http://www.contextures.com/xlDataVal02.html