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

[RinorM]

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

Please Login or Register  to view this content.

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.

[arlu1201]

Is this solved?

If you solve a problem yourself before anyone else has responded, please take a moment to describe your solution, chances are some other member will benefit.

[RinorM]

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:

Please Login or Register  to view this content.

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 !