Add values from diffrent sheets to drop down menu and ignoring blank cells.

Hi! I have this code which takes data from one sheet and when running a macro it displays a dropdown.

With Sheets("Indata").Range("Q6").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:="=IPE!$J$307:$J$324"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

How can I in Formula1 add values from 2 more sheets named HEA and HEB. They are within the same range as "IPE!$J$307:$J$324". How can I also write the code that within range J307:J324 it skips the blank cells in that column

Thankful for any kind of help.

Hi! ofcourse I will provide the answer. I didnt code it in VBA though.

How to import values from diffrent sheets into the code above:

To do this I actually copied and pasted the values from the other sheets into a new sheet which instead contained a very long column.

How to skip blanks:

This large column I created now contains alot of blanks etc. To skip theese I simply followed this tutorial: http://www.youtube.com/watch?v=6PcF04bTSOM

I created The formula 1 and 2 like in the example. This gives me a column which now contains values form all sheets plus no blanks. And it is also dynamic, which suited for me. To this list i assigned a new name:

With Sheets("Indata").Range("Q6").Validation
    .Delete
    .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, _
        Formula1:="=ListWithNoBlanks"
    .IgnoreBlank = True
    .InCellDropdown = True
End With

The code above creates a drop down list with values from "ListWithNoBlanks". This list then is dynamic and contains the values you want. Hope that helped, have a nice day !