Formulas in data validation range causing blanks

[Stuepef]

I have a workbook where users can input job titles in two different ranges (A2:A6 & A8:A12). Only one range would be filled at a time, but I want to use one data validation list for both ranges without blanks showing up within the list. The problem is if I use formulas to generate the range for the list, it counts those cells and shows up as blanks. Is there a way to make a list that incorporates both ranges without blanks showing up in the list?

Workbook:

[TABLE="width: 199"]
<tbody>[TR]
[TD][/TD]
[TD]Job Title/Hours/Status[/TD]
[/TR]
[TR]
[TD="align: left"]A2[/TD]
[TD="align: left"]Officer[/TD]
[/TR]
[TR]
[TD="align: left"]A3[/TD]
[TD="align: left"]Part Time[/TD]
[/TR]
[TR]
[TD="align: left"]A4[/TD]
[TD="align: left"]Full Time[/TD]
[/TR]
[TR]
[TD="align: left"]A5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Job Title/Hours/Status[/TD]
[/TR]
[TR]
[TD="align: left"]A8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: left"]A12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

I made an IF statement to make one range on a different sheet to consolidate, but the formula that have blanks show up in the list since they aren't truly blank. Here is the formula used within the data validation list:

=OFFSET($G2,0,0,COUNTA($G:$G)-1,1)

Desired Result regardless of what range the information is in:
Officer
Part Time
Full time

Thank you for your help!

 

[jorismoerings]

Hi,

See if this helps:

Book1
	A	B	C	D	E	F	G	H	I
1	Job Title/Hours/Status			Job Title/Hours/Status
2	Officer			Officer				DV list	Test
3	Part Time			Part Time
4	Full Time			Full Time
5				Test
6
7	Job Title/Hours/Status
8	Test
9	Test
10
11
12
13
Sheet1

Cell Formulas
Range	Formula
D2	{=IFERROR(IFERROR(INDEX($A$2:$A$6, MATCH(0, COUNTIF($D$1:D1, $A$2:$A$6)+($A$2:$A$6=""), 0)), INDEX($A$8:$A$13, MATCH(0, COUNTIF($D$1:D1, $A$8:$A$13)+($A$8:$A$13=""), 0))),"" )}
Press CTRL+SHIFT+ENTER to enter array formulas.

The list could potentially run from D2 till D12.

So i created a dynamic named range using this formula

=OFFSET(Sheet1!$D1,0,0,ROWS(Sheet1!$D$2:INDEX(Sheet1!$D$2:$D$12,SUMPRODUCT(--(Sheet1!$D$2:$D$12<>"")))),1)

Then enter the named range as a list in the data validation.

Here's a link to my example https://app.box.com/s/w88mnxo417tlue16i4rkomkzqzdc52sg

 

[Stuepef]

thank you!