empty cells in the drop down list
 

Hi

Im working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as €œServers€� (I need the
empty cells for future items)
In F1 created drop down list and its data validation is €œ=servers€�
When I pressing on the drop down box, the 5 items appear but also appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box? I
dont want every time that Im adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

In the validation criteria dialogue window - allow - select 'list' - click
'ignore blank' - ok

Please don't forget to click if this was useful

"NirOrtal" wrote:

Hi

Im working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as €œServers€� (I need the
empty cells for future items)
In F1 created drop down list and its data validation is €œ=servers€�
When I pressing on the drop down box, the 5 items appear but also appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box? I
dont want every time that Im adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

Use a dynamic range formula...

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet1 !$A$2:$A$11))

Use the appropriate sheet name.

OKClose

Then, as the source for the drop down use =Servers

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
Hi

I'm working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as "Servers" (I need the
empty cells for future items)
In F1 created drop down list and its data validation is "=servers"
When I pressing on the drop down box, the 5 items appear but also appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box? I
don't want every time that I'm adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

ignore blank is by default.
try to re-mark it, but the same

"Arceedee" wrote:

In the validation criteria dialogue window - allow - select 'list' - click
'ignore blank' - ok

Please don't forget to click if this was useful

"NirOrtal" wrote:

Hi

Im working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as €œServers€� (I need the
empty cells for future items)
In F1 created drop down list and its data validation is €œ=servers€�
When I pressing on the drop down box, the 5 items appear but also appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box? I
dont want every time that Im adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

where i put
"=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet 1!$A$2:$A$11))"? in the
cell of the drop down list?

"T. Valko" wrote:

Use a dynamic range formula...

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet1 !$A$2:$A$11))

Use the appropriate sheet name.

OKClose

Then, as the source for the drop down use =Servers

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
Hi

I'm working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as "Servers" (I need the
empty cells for future items)
In F1 created drop down list and its data validation is "=servers"
When I pressing on the drop down box, the 5 items appear but also appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box? I
don't want every time that I'm adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

No, you put it he

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

Enter the formula in the little box to the right of where it says: Refers
to.

That box looks too small but it'll fit!

Tip: write the formula in a worksheet cell then just copy it and paste it
into that little box.


--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
where i put
"=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet 1!$A$2:$A$11))"? in the
cell of the drop down list?

"T. Valko" wrote:

Use a dynamic range formula...

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet1 !$A$2:$A$11))

Use the appropriate sheet name.

OKClose

Then, as the source for the drop down use =Servers

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
Hi

I'm working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as "Servers" (I need
the
empty cells for future items)
In F1 created drop down list and its data validation is "=servers"
When I pressing on the drop down box, the 5 items appear but also
appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box?
I
don't want every time that I'm adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

THANX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

"T. Valko" wrote:

No, you put it he

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

Enter the formula in the little box to the right of where it says: Refers
to.

That box looks too small but it'll fit!

Tip: write the formula in a worksheet cell then just copy it and paste it
into that little box.


--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
where i put
"=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet 1!$A$2:$A$11))"? in the
cell of the drop down list?

"T. Valko" wrote:

Use a dynamic range formula...

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet1 !$A$2:$A$11))

Use the appropriate sheet name.

OKClose

Then, as the source for the drop down use =Servers

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
Hi

I'm working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as "Servers" (I need
the
empty cells for future items)
In F1 created drop down list and its data validation is "=servers"
When I pressing on the drop down box, the 5 items appear but also
appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown box?
I
don't want every time that I'm adding new items, to modify the Name Box

Thanx

empty cells in the drop down list
 

You're welcome!

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
THANX!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

"T. Valko" wrote:

No, you put it he

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

Enter the formula in the little box to the right of where it says: Refers
to.

That box looks too small but it'll fit!

Tip: write the formula in a worksheet cell then just copy it and paste it
into that little box.


--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
where i put
"=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet 1!$A$2:$A$11))"? in
the
cell of the drop down list?

"T. Valko" wrote:

Use a dynamic range formula...

Create the named range...
Formulas tabDefined NamesName ManagerNew
Name: Servers
Refers to:

=Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$11,COUNTA(Sheet1 !$A$2:$A$11))

Use the appropriate sheet name.

OKClose

Then, as the source for the drop down use =Servers

--
Biff
Microsoft Excel MVP


"NirOrtal" wrote in message
...
Hi

I'm working with Excel 2007

I have a list of 5 items from A2:A6.
I highlighted A2:A11 and named it in the Name Box as "Servers" (I
need
the
empty cells for future items)
In F1 created drop down list and its data validation is "=servers"
When I pressing on the drop down box, the 5 items appear but also
appears
the space of the 5 empty cells (A7:A11)
How can I eliminating the empty cell to be appear in the dropdown
box?
I
don't want every time that I'm adding new items, to modify the Name
Box

Thanx