Insert validation list with =INDIRECT in VBA

Hi all,

i have the following part of a script.

Please Login or Register  to view this content.

It creates a copy of the selected line, and paste it 1 row under.
I would like to let range E20 (after running the script) create a validation list with INDIRECT function, which refers to range C17.

Do any of you have a clue to script this?

Regards,
Crispy85

Hi Richard Buttrey,

I hope you can follow me because i have a hard time explaining it. I work with validation list via =INDIRECT function.
whenever i add an extra line, the =INDIRECT will shift for example from =INDIRECT(C17) to =INDIRECT(C18).

Now, i would be able to fix the range with $C$17 but as you will see in my example sheet, it won't do what i want...

What i would like is = if line inserted, fix the indirection range so it won't shift.

But i don't really know how to script this

Hi S,

Thanks for your help so far.
As i said, i'm having a difficult time explaining it so your help is much appreciated!!

There can be an unlimited amount of domains and each time a new "domain" is created it does so by copying and inserting row 1:6 in row 16.
So in cell E4, data validation is set to list =INDIRECT(C2). So when the new "domain" is created it sets it in Cell E19 with =INDIRECT(C17). That's the reason why i can't make it absolute, because then it will always refer to $C$2. Do you understand what i mean?

So the easiest way to fix this (i think) is:

Whenever Add line 1 or Add line 2 is clicked, it inserts a line above, but in the one that is under the inserted one, it should change the reference to absolute.
So in my example sheet. When the link Add Line in I10 is clicked, a new line will be inserted in row 19. The existing row 19 will go down 1 row and it's reference to C17 should be made absolute.

Maybe this will help so you can see what my problem is, when you follow these steps:


1. Click ADD DOMAIN in I11 3 times
2. Click C17 and choose "ABC"
3. Now the dropdownlist in E19 is activated
4. Pick a option from the list
5. Now click ADD LINE in I10
6. The dropdown list that was first in E19 and now in E20 doesn't work no more.

Thanks
Crispy85

Hi S!

I now added the sheet3, with what the result COULD be like (depending on how many domains and extra lines are inserted.)

all the dropdown lists in column E refer to their "domain".

Many thanks.

Hi S,

Yeah, You are correct and that's the way it should be.
However, the "ADD DOMAIN" option, copies and inserts row 1:6 in row 16. So how would this work? In cell E4 the data validation will be set to Indirect(C$2). When "ADD Domain" now is clicked, it keeps the validation Indirect(C$2) while it should be Indirect(C$17).

Hi Richard,

Unfortunately, this doesn't fix it
What it does with your given formula is just make a dropdown list with the info filled in in cell C17.
It should make a dropdown list with ABC or DEF or GHI depending on what is been chosen in C17.