Named Range Problem

[solnajeff]

Hi

I have a question that I hope someone can assist me with.

I have ten named ranges relating to classes that are selected and then a dependent list is used for students to look up their names and enter a pin code to gain access to a test.

I would normally use dynamic ranges but I understand that the indirect function will not work with a dynamic range.

I have tried also using VBA to recreate the ranges if they change, but although I can recreate dynamic ranges I cannot get the "RefersTo" section to function correctly for a static range without empty cells.

My question is, is there a way to use dependent lists with dynamic ranges? Alternatively with a static range that is larger than the data contained within it, is it possible to have the first cell displayed rather than the first empty cell?

Many thanks for your comments

Regards


Jeff

[protonLeah]

Several weeks ago there was a question on dynamic ranges ( I forget where or who supplied the solution); but, try this sol'n from those threads:
Given: a changable list on Sheet2 column A,
Then, define a name, myList, such that:

Please Login or Register  to view this content.

This should get you close with a few tweaks of the OFFSET parameters.
Edit, see:
http://www.contextures.com/xlNames01.html#Dynamic

[solnajeff]

HI

Thanks for your comments.

I know how to create dynamic named ranges but the problem is using dependent named ranges via the INDIRECT function which does not work with dynamic ranges. At least as far as I can establish.

Regards

Jeff

[solnajeff]

Hi

I have found a workaround to the problem as follows, and did it this particular way so the worksheet presentation does not have to be changed.

Data is entered by the user and as the sheet needs to be hidden after use, all code is attached to the button for hiding the sheet.

All named ranges are generated using VBA as dynamic ranges (could not get the code to work for static ranges), after each dynamic range has been generated, the actual range is derived using "RefersToRange.Address" and the named range converted to a static one.

I am sure there are simpler ways, but this works well for me.

Regards

Jeff