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.
I presume it is a combination of INDIRECT and DYNAMIC ranges using offset but I can't get it to work.