Indirect function conflicting with dynamic range name under dropdown list

Question

Hi all

Dynamic range named (myrange) is defined based on the following formula:

=sheet2!$P$4:INDEX(sheet2!$P$4:$P$10000,COUNTIF(sheet2!$P$4:$P$10000,"?*"))

With above formula following is achieved:

1. Blank cells are ignored in drop down list
2. visible scrolling area in drop dawn is limited to last nonblank cell in the list
3. Drop down always starts from the beginning of the list.

However I’m facing two issues here:

If data validation is set as:

=myrange

It works fine, but problem is that user is able to enter any values in the cell. (That is minor because VBA behind verifies the value before transfer to data table.)

But if data validation is set as:

=indirect(“myrange”)

Then I’m getting error massage: “The source currently evaluates to an error”…

In fact I need to use indirect for dependent lists and above mentioned issues are stopping to complete part of my project I’m working on. Thanks in advance for you swift support resolving dynamic range issue.

Regards

Answer 1

Hi,

For the dependent validation list, use =INDIRECT(A2) in the Data > Validation > Allow > List.

A2 has =myrange in Data > Validation > Allow > List.  Ensure that cell A2 has some selection.

Hope this helps.

Answer 2

hi, have a look here, in general random length arrays will not be accepted in DV.

Answer 3

Thanks folks for your suggestions. I will go through trying all alternatives and will come back with feedback if any success.

Thanks again for your support and regards

Appreciated :)

Answer 4

Hi Bernie

Unfortunately it makes no difference. I tried different ways to solve this problem. My only option is to use indirect function, but it fails.

In fact I have several check boxes for different functions assigned on this worksheet and command button to execute data transfer to another hidden sheet. In addition there is Private Sub Worksheet_Change(ByVal Target As Range) code running behind the scene. Regarding several drop down list in this sheet there are no link with mentioned codes, accordingly they should not be interrupting and causing failure.

All together I have 5 dropdown lists. Previously I used function =indirect(“myrange”) but range names were defined as static. All was Ok, but blanks were visible and list was opening with last item first on the screen. In other word you should scroll up to get to the beginning of the list.

Naming list using formulas for dynamic range I faced above mentioned problems giving error as soon I try to use “Indirect” for the dropdown list.

And one more thing for 3rd dropdown list I had reference as =INDIRECT(A50) to enable dependent lists.

I desperately need solution for this issue.  What can be a reason? Any idea?

I hope above described  gives you better understanding of the issue.

Thanks again for your time

Cheers

Answer 5

Does it help if you redefine your dynamic range as

=OFFSET(Sheet2!$P$4,0,0,COUNTIF(Sheet2!$P$4:$P$10000,"?*"),1)

With the list option of DV, the user should not be able to enter other values.