Data Validation and Indirect Function

Hi

I have a problem regarding using lists with the indirect function. I have a series of class names and each has a named range associated.

Each class has a secondary named range that is used for including or excluding names and that removes each name as it is used.

I am trying to use Indirect so that by entering the class number the relevant list appears -

However the function will not work in the sheet I am working on, I have tried it in another sheet and everything is fine, so the problem is in the workbook, unfortunately it is a rather large workbook as well.

I was wondering if anyone else had come across something similar.

Thanks in advance

Regards


Jeff

I can't say I've experienced that. Since the functionality works in a new workbook, I'd suggest copying all of the worksheets to a new workbook and testing the functionality. (You may have to re-define the named ranges in the new workbook.)

Otherwise, zip and post a copy of the workbook and someone here can take a look at it.

Hi

I have found out the cause of my problem and have reproduced it on the attached sheet.

Everything works fine when the named range is static, however with a dynamic range defined using OFFSET nothing.

The ranges if used directly both work no problem, it seems just to be a problem in conjunction with OFFSET and INDIRECT.

I cannot see how to get around it at the moment.

Any advice would be much appreciated.

Regards

Jeff

Debra Dalgleish seem to be the expert on Dropdown lists and she used the entire column in this example which suggests to me it can't be done

Might be dropping her an e-mail (See below) with the question

[email protected]

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

VBA Noob