Ignoring blanks in a dynamic named range

**jman0707** · 10-07-2008, 11:17 AM

I used each of the following codes for dynamic name ranges.

=OFFSET(INPUT!$L$4,0,0,SUMPRODUCT(--(Detailed!$L$4:$L$27<>"")),1)

=OFFSET(INPUT!$L$4,0,0,COUNTA(INPUT!$L:$L)-1,1)

And each one ignores blanks at the END of the list. I have a list that users can change the names in and delete names. I have a dropdown list that is validated based on this list, and it needs to ignore blanks IN THE MIDDLE of the list as well. So no matter what cells they clear or rearrange, the dropdown list will only contain any cells with a value in it and ignore all blanks. The list is in L4:L27 on the INPUT worksheet.

This is Excel 2007 and the worksheet and cell references at the top are accurate for my worksheet. Thanks!

**sglife** · 10-07-2008, 10:47 PM

Here is a way
First, use the following formula to generate a new list without blank cells


=IF(ROW()>COUNTA($A$1:$A$1000),"",INDEX($A$1:$A$1000,SMALL(IF(ISBLANK($A$1:$A$1000),"",ROW($A$1:$A$1000)),ROW())))

Then defined your dynamic name base on the new list

**jman0707** · 10-08-2008, 05:52 PM

Can you explain the steps to do this in a little more detail? I didn't quite follow. My list is from L4 to L27, and then when users delete names within there, I'd like the range to ignore those so when I validate another cell on another worksheet with a dropdown referencing this list, I'd like the blanks in between not to show up.

I went to Formulas > Define Name and pasted this code in (your code, but with all the A1:A100 switched to L4:L27).

=if(row()>counta($l$4:$l$27),"",index($l$4:$l$27,small(if(isblank($l$4:$l$27),"",row($l$4:$l$27)),row())))

I called the list "Products2." Then I went to the cell where I wanted the dropdown list to show up. I went to Data Validation and selected "List" and for the list name I put =Products2. It then told me that there was an error in the formula of the list. So I'm not sure if I even did it right. Any feedback will be great, thanks!

**sglife** · 10-08-2008, 11:08 PM

For your range L4 to L27
Use this


M4=IF(ROW()-3>COUNTA($L$4:$L$27),"",INDEX($L$1:$L$27,SMALL(IF(ISBLANK($L$4:$L$27),"",ROW($L$4:$L$27)),ROW()-3)))

Then copy down

Define name , eg ABC


=INDIRECT(CONCATENATE("$M$4:$M",COUNTA($M$4:$M$27)-COUNTIF($M$4:$M$27,"")+3))

Then make your validation list , source "=ABC"

**jman0707** · 10-09-2008, 11:10 AM

In this code

M4=if(row()-3>counta($l$4:$l$27),"",index($l$1:$l$27,small(if(isblank($l$4:$l$27),"",row($l$4:$l$27)),row()-3)))

after Index, is that "L1" supposed to be "L4"? Am I pasting this in some cell (M4 or some other) near L4 to create another list, and this new list will be the source of my data validation? I tried dragging it down from M4 to M27 and I got #NUM! for all but the first cell. I'm guessing some of the dollar signs might need to be removed??

And do I paste this code in the "define range" area to create the new list for validation?

=indirect(concatenate("$m$4:$m",counta($m$4:$m$27)-countif($m$4:$m$27,"")+3))

Sorry, I just need more of a step by step because I need to ensure I have cells open to paste more formula into, and I need to know where each of the codes you gave me goes. Thanks!