Combo box or validation list without blanks

[rayw1152]

Hi All,

I am now here but I really find it hard to get a solution to my project.

I have a big list which generated from another tab by vLookup's and a another vlookup brings another fields based on a date, and as the list gets big and brings only data that matches the date in the row, of course I ended up with lots of blanks which they are in this format "" of course based on the function: (=IFERROR(VLOOKUP($B2,Sheet5!A:N,7,),"")

Now I want either to get a validation list or Combo Box based on these data to show so the user can select it, but my problem is I get all the blanks between the data and everywhere.

I would really appreciate it if someone could help.

I found something in youtube and it does work for small data but when i get to a big data it doesn't no idea why.

I've attached the file if some could help I will be grateful.

regards,

Ray

[wallyeye]

You can change your DrangeS to:

=OFFSET(Data!$H$2,0,0,COUNT(Data!H:H),1)

which incorporates the lesser known range parameters of the Offset command.

[rayw1152]

Thank you walleye..

This is very helpful..

Regards,