Using Named Lists from other worksheets

[neilberl]

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!

 

[erik.van.geit]

Hi, neilberl
Welcome to the Board !!!!!

do a search on the board for "dynamic range"
this would be an example
http://www.mrexcel.com/board2/viewtopic.php?t=85210

kind regards,
Erik

 

[neilberl]

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?

 

[erik.van.geit]

there is $A$48

which is from A10->A99

you have to update to $A$10

 

[neilberl]

See below

 

[neilberl]

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??

 

[neilberl]

HELP!

Can anyone help me with this????

 

[erik.van.geit]

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

{=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

=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
	A	B	C	D
1	BlanksRange	NoBlanksRange
2	AAA	AAA
3	BBB	BBB
4		EEE
5		DDD
6	EEE	GGG
7		HHH
8	DDD	ZZZ
9	GGG	RRR
10		RRR
11		SSS
12		JJJ
13	HHH	MMM
14	ZZZ	FFF
15
16
17	RRR
18
19	RRR
20	SSS
21
22	JJJ
23	MMM
24
25	FFF
Sheet1

I didn't take care of your ranges: please adapt to your suits

kind regards,
Erik