Dynamic Data validation Drop down

**SPACKlick** · 10-15-2012, 08:38 AM

Hi there, I have been able to do this previously and cannot for the lfie of me remember how I did it.

I have a Spreadsheet set out as following

Sheet 1
Row A1:F1 = Column titles
Row 2 - BLANK (can have information put in columns G and beyond)
A3:A1002 - Names (all entries unique)
B3:D1002 - information about the data
E3:E1002 - Either 1 or 0
F3:F1002 - Currently if E=1, Name else Blank

F can be changed or entirely removed if needs be.

I need to create a drop down box on Sheet 2 that will populate with only those names (from column A) which are available (column E is 1) without blanks.

I'm sure previously I used a formulae to create a list without blanks in column G, used a dynamic named range to create the dropdown. But I cannot remember the formula for column G to populate the list for the dynamic named range.

Any advice?

**Fotis1991** · 10-15-2012, 08:49 AM

Hi

Take a look to this and if you can not handle this, pls upload a small sample workbook.

http://www.contextures.com/xlNames01.html

**SPACKlick** · 10-15-2012, 09:15 AM

I'm OK with dynamic named ranges, my issue is that the data is not consecutive, there are "" blanks or 0s in between data I need.

I don't know how to upload a sample sheet.

**Fotis1991** · 10-15-2012, 09:17 AM

To attach a file, push the button with the paperclip (or scroll down to the Manage Attachments button), browse to the required file, and then push the Upload button.

**SPACKlick** · 10-18-2012, 09:30 AM

Resoved using the following formula

in column AD ={IFERROR(INDEX(BlanksRange,SMALL((IF(LEN(BlanksRange),ROW(INDIRECT("1:"&ROWS(BlanksRange))))),ROW(A1)),1),"")}

And defining my dynamic range as
=OFFSET('Skills Database'!$AD$3, 0, 0, COUNTA('Skills Database'!$AD$3:$AD$959)-COUNTIF('Skills Database'!$AD$3:$AD$959,""),1)