Ignore blank cells in a named range when using dependent data validation

**JackyJ** · 09-15-2010, 06:35 AM

I am using dependant data validation in cell ‘Sheet2!$B2’. It’s formula is ‘=INDIRECT($A2)’.
The dropdown menu in ‘Sheet2!$B2’ then references a named range in ‘Sheet1!$L:$L’, which at times has one or more blank cells at the top of the column. I would like to ignore the blank cells in the column so that only the populated cells in the named range appear in the drop down menu.
I acquired the following formula from the net to replace ‘Sheet1!$L:$L’ in the name manager, but I am unable to make it work.
=OFFSET(Sheet1!$L$2,0,0,MATCH("*",Sheet1!$L$2:$L$100,-1),1)
Any help would be greatly appreciated.

I'm using Excel 2007, Vista

**NBVC** · 09-15-2010, 09:12 AM

You can not set data validation using an indirect reference to a range created with a formula... see here: http://www.contextures.com/xldataval02.html#Dynamic

Instead as per above... name L1 in Sheet1, something like List and then name the range L1:L100 in Sheet1, ListCol...

Then go to B2 of Sheet2 and invoke Data Validation and replace the =Indirect(A2) with:

Please Login or Register  to view this content.

**JackyJ** · 09-16-2010, 09:14 AM

That works well, NBVC.
Problem is I still have the blanks in the dropdown menu.
I’ve posted a sample of my spreadsheet. I’ve used your formula in B2 of Sheet2 and amended the Melbourne named range to work with it.
I’ve been using an array formula to get rid of the blank cells, but this is a problem when the tables are large. When entering new data into the tables, via a userform, the computer lags severely with the load. I want to remove the array formulas to improve the programs performance. Any ideas?
Thanks for your time.

**NBVC** · 09-16-2010, 12:08 PM

My interpretation was based on this from your original post...

which at times has one or more blank cells at the top of the column

To remove blanks amongst the data, you will need to recreate the list using formulas for each column elsewhere or use some vba...

Check here for help on the VBA way:

http://www.ozgrid.com/forum/showthre...t=59450&page=1

http://www.computing.net/answers/off...menu/9098.html

if you want to do it with formulas, let me know...

**JackyJ** · 09-17-2010, 03:44 AM

I’m not sure which is the best way to go: VBA or Formulas. Using array formulas slowed the computer down appreciably. (The fully working spreadsheet will have around 300 to 500 rows and at least 13 ranges, all of which will need blanks removed from them.)
I’ve gotten part way through the problem with the vba information you have supplied me. The following is what I came up with.

Please Login or Register  to view this content.

This code does half the job. It gets rid of the blanks, but places the amended list in a dropdown menu in X1. I wish to populate ‘X1:X’ with this amended list and no dropdown menu.
Are you able to assist?