Validation list with blanks at the bottom
 

I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list.

I have used the this code:

=INDIRECT("sheet2!A2:A200")

I have used this code because the list is constantly updated and this will
mean if I enter a new value at A108 I wont have to update the validation list
code.

However my problem is on the list when its dropped down it does not ignore
the blanks. It shows 93 blank spaces after the values.

I have tried clicking the Ignore-Blanks tick box yet it does not work. any
ideas?

P.S am usuing Excel 200

Andrew

Validation list with blanks at the bottom
 

I think you'll be better off using a dynamic range name.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

Andrew wrote:

I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list.

I have used the this code:

=INDIRECT("sheet2!A2:A200")

I have used this code because the list is constantly updated and this will
mean if I enter a new value at A108 I wont have to update the validation list
code.

However my problem is on the list when its dropped down it does not ignore
the blanks. It shows 93 blank spaces after the values.

I have tried clicking the Ignore-Blanks tick box yet it does not work. any
ideas?

P.S am usuing Excel 200

Andrew

--

Dave Peterson

Validation list with blanks at the bottom
 

fantastic il try it now

"Dave Peterson" wrote:

I think you'll be better off using a dynamic range name.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

Andrew wrote:

I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list.

I have used the this code:

=INDIRECT("sheet2!A2:A200")

I have used this code because the list is constantly updated and this will
mean if I enter a new value at A108 I wont have to update the validation list
code.

However my problem is on the list when its dropped down it does not ignore
the blanks. It shows 93 blank spaces after the values.

I have tried clicking the Ignore-Blanks tick box yet it does not work. any
ideas?

P.S am usuing Excel 200

Andrew

--

Dave Peterson

Validation list with blanks at the bottom
 

It worked your a star

"Dave Peterson" wrote:

I think you'll be better off using a dynamic range name.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

Andrew wrote:

I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list.

I have used the this code:

=INDIRECT("sheet2!A2:A200")

I have used this code because the list is constantly updated and this will
mean if I enter a new value at A108 I wont have to update the validation list
code.

However my problem is on the list when its dropped down it does not ignore
the blanks. It shows 93 blank spaces after the values.

I have tried clicking the Ignore-Blanks tick box yet it does not work. any
ideas?

P.S am usuing Excel 200

Andrew

--

Dave Peterson

Validation list with blanks at the bottom
 

I'm sure Debra appreciates that her site has helped another user.

Andrew wrote:

It worked your a star

"Dave Peterson" wrote:

I think you'll be better off using a dynamic range name.

Check how Debra Dalgleish does it:
http://contextures.com/xlNames01.html#Dynamic

Andrew wrote:

I have a list of infomation from cells A2:A107 which is used as a reference
for a validation list.

I have used the this code:

=INDIRECT("sheet2!A2:A200")

I have used this code because the list is constantly updated and this will
mean if I enter a new value at A108 I wont have to update the validation list
code.

However my problem is on the list when its dropped down it does not ignore
the blanks. It shows 93 blank spaces after the values.

I have tried clicking the Ignore-Blanks tick box yet it does not work. any
ideas?

P.S am usuing Excel 200

Andrew

--

Dave Peterson


--

Dave Peterson