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

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

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

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

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

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

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

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