# Using Named Lists from other worksheets

#### neilberl

##### New Member
In my scenario, I have defined a Name (for example, Developers which is from A10->A99.

However, at this point, i only have 10 Developers.

When I reference the Name in another worksheet (using Data Validation/List), there are several blank records displaying (interestingly enough, not 90, but a subset of blank records). I would like to suppress these blank records from the dropdown.

Any idea?

As we continue using the Workbook, the additional cells will be getting filled out, so I wanted the dropdown in the other worksheets to be "dynamic."

Thanks!

### Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Thanks for the help, but I still can't get this right.

I tried:

BigStr = =REPT("z",255)

And DB = =Sheet1!\$A\$48:INDEX(Sheet1!\$A:\$A,MATCH(BigStr,Sheet1!\$A:\$A))

But when I do the Validation, only the Last Value in column A displays, not all values except the blanks.

What am I missing?

See below

Update -

I have one Name (=Names) defined as:
=Sheet1!\$A\$5:INDEX(Sheet1!\$A\$5:\$A\$3000,MATCH(REPT("z",255),Sheet1!\$A\$5:\$A\$3000))

Then I have a Data Validation of List, =Names

Let's say Column A has data:
A5 = Row 1
A6 = Row 2
A7 = [BLANK]
A8 = Row 3
A9 = [BLANK]
A10 = Row 4

In the dropdown in Column B, the values display as:
Row 1
Row 2
[BLANK]
Row 3
[BLANK]
Row 4

I was hoping this was going to display as:
Row 1
Row 2
Row 3
Row 4

Any other ideas??

HELP!

Can anyone help me with this????

Can anyone help me with this????
YES! but the board is like a room: sometimes we're leaving
see my quote: if you think I forgot you, you can always email a reminder

This can be solved using an extra column.
As I find it difficult to explain in words, this sample will clarify.
1.
name two ranges
BlanksRange NoBlanksRange
same size (if you want 3000 rows)

2.
apply the formulas as you can see in the sample
Code:
``{=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))}``
entered using Shift-Control-Enter and copied down
this formula is on my machine since about 2 years, at that moment didn't care about authors
(sorry dear unknown author)

3.
Your range to "feed" the validationlist, will be defined as
Code:
``=Sheet1!\$B\$2:INDEX(Sheet1!\$B\$2:\$B\$65536, MATCH("*",Sheet1!\$B\$2:\$B\$65536,-1))``
this is another formula (compared to the one you used thusfar) which is needed when refering to cells which contain blanks as the result of a formula
NOBLANKS.XLS
ABCD
1BlanksRangeNoBlanksRange
2AAAAAA
3BBBBBB
4EEE
5DDD
6EEEGGG
7HHH
8DDDZZZ
9GGGRRR
10RRR
11SSS
12JJJ
13HHHMMM
14ZZZFFF
15
16
17RRR
18
19RRR
20SSS
21
22JJJ
23MMM
24
25FFF
Sheet1

kind regards,
Erik

Replies
3
Views
238
Replies
15
Views
293
Replies
1
Views
554
Replies
0
Views
460
Replies
1
Views
174

1,221,523
Messages
6,160,323
Members
451,637
Latest member
hvp2262

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back