Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet Normal1.
I do a Data Validation in a cell in sheet Normal1 with List and "=AllParts".
I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the whole
list

I do it all again in a cell in the sheet where I want it, call it BadSheet,
and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first AllParts
list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto

What does the formula for AllParts (refers to ) look like. Are you using
relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet Normal1.
I do a Data Validation in a cell in sheet Normal1 with List and
"=AllParts".
I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the
whole
list

I do it all again in a cell in the sheet where I want it, call it
BadSheet,
and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first AllParts
list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto

Tom
Thanks for your reply. The naming of the list is done by code. The
code copies/pastes (shorter) lists from several sheets to make one longer
list. All this is within a For loop because there are two final lists to be
setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is absolute.
Otto
"Tom Ogilvy" wrote in message
...
What does the formula for AllParts (refers to ) look like. Are you using
relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...
Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet
Normal1.
I do a Data Validation in a cell in sheet Normal1 with List and
"=AllParts".
I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the
whole
list

I do it all again in a cell in the sheet where I want it, call it
BadSheet,
and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first
AllParts
list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto

Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:
Tom
Thanks for your reply. The naming of the list is done by code. The
code copies/pastes (shorter) lists from several sheets to make one longer
list. All this is within a For loop because there are two final lists to be
setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is absolute.
Otto
"Tom Ogilvy" wrote in message
...

What does the formula for AllParts (refers to ) look like. Are you using
relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...

Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.

I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".

I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the

whole

list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,

and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first

AllParts

list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new blank
sheet and rebuilt the Data Validation cells but in the new sheet (without
copying anything from BadSheet). All works well. Does this indicate to you
that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...
Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:
Tom
Thanks for your reply. The naming of the list is done by code. The
code copies/pastes (shorter) lists from several sheets to make one
longer
list. All this is within a For loop because there are two final lists
to be
setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is absolute.
Otto
"Tom Ogilvy" wrote in message
...

What does the formula for AllParts (refers to ) look like. Are you
using
relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...

Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.

I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".

I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the

whole

list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,

and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first

AllParts

list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
I had BadSheet protected off and on while I was having this problem.
Looking at it now I don't see that I need to protect the sheet at all, so I
haven't used sheet protection on the new sheet. BTW, the problem is there
with BadSheet whether or not the sheet is protected. But is there something
about Data Validation and sheet protection that can cause sheet corruption?
Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...
Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:
Tom
Thanks for your reply. The naming of the list is done by code. The
code copies/pastes (shorter) lists from several sheets to make one
longer
list. All this is within a For loop because there are two final lists
to be
setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is absolute.
Otto
"Tom Ogilvy" wrote in message
...

What does the formula for AllParts (refers to ) look like. Are you
using
relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...

Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.

I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".

I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the

whole

list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,

and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first

AllParts

list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:
Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new blank
sheet and rebuilt the Data Validation cells but in the new sheet (without
copying anything from BadSheet). All works well. Does this indicate to you
that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...

Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:

Tom
Thanks for your reply. The naming of the list is done by code. The
code copies/pastes (shorter) lists from several sheets to make one

longer

list. All this is within a For loop because there are two final lists

to be

setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is absolute.
Otto
"Tom Ogilvy" wrote in message
...


What does the formula for AllParts (refers to ) look like. Are you

using

relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .


Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.


I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".


I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get the

whole


list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,


and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different list.

I go to the cell in BadSheet, clear the Data Validation, redo the Data
Validation to =AllParts. I get the same 14 items from the first

AllParts


list.

Is this sheet corrupted or have I lost it? Thanks for your help. Otto






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
Bingo! With BadSheet selected, Insert - Name - Define showed "BadSheet"
at the far right. What does that mean? When I then clicked on AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet "All
Parts").
Can you please, please, please explain to me what I did to get into this
mess? And what I need to watch out for so that I don't do it again? Does
it have to do with references while in a With - End With construct? I ask
that because I was in a With Sheets("All Parts") construct when I named the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...
Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:
Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new
blank
sheet and rebuilt the Data Validation cells but in the new sheet
(without
copying anything from BadSheet). All works well. Does this indicate to
you
that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...

Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:

Tom
Thanks for your reply. The naming of the list is done by code.
The
code copies/pastes (shorter) lists from several sheets to make one

longer

list. All this is within a For loop because there are two final lists

to be

setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is
absolute.
Otto
"Tom Ogilvy" wrote in message
...


What does the formula for AllParts (refers to ) look like. Are you

using

relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .


Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.


I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".


I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get
the

whole


list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,


and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different
list.

I go to the cell in BadSheet, clear the Data Validation, redo the
Data
Validation to =AllParts. I get the same 14 items from the first

AllParts


list.

Is this sheet corrupted or have I lost it? Thanks for your help.
Otto






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed "BadSheet"
at the far right. What does that mean? When I then clicked on AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet "All
Parts").
Can you please, please, please explain to me what I did to get into this
mess? And what I need to watch out for so that I don't do it again? Does
it have to do with references while in a With - End With construct? I ask
that because I was in a With Sheets("All Parts") construct when I named the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this indicate to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by code.

The

code copies/pastes (shorter) lists from several sheets to make one

longer


list. All this is within a For loop because there are two final lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like. Are you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get

the

whole



list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different

list.

I go to the cell in BadSheet, clear the Data Validation, redo the

Data

Validation to =AllParts. I get the same 14 items from the first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Debra
RngName is straight text without any reference to the sheet. The sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...
How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed
"BadSheet"
at the far right. What does that mean? When I then clicked on
AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet
"All
Parts").
Can you please, please, please explain to me what I did to get into this
mess? And what I need to watch out for so that I don't do it again?
Does
it have to do with references while in a With - End With construct? I
ask
that because I was in a With Sheets("All Parts") construct when I named
the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this indicate
to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by code.

The

code copies/pastes (shorter) lists from several sheets to make one

longer


list. All this is within a For loop because there are two final
lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like. Are you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get

the

whole



list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different

list.

I go to the cell in BadSheet, clear the Data Validation, redo the

Data

Validation to =AllParts. I get the same 14 items from the first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

You could create a copy of the workbook, and use Jan Karel Pieterse's
Name Manager utility to delete all versions of the "AllParts" name. You
can download a copy he

http://www.bmsltd.co.uk/MVP/Default.htm

Then, step through the code, to see where the names are being created.


Otto Moehrbach wrote:
Debra
RngName is straight text without any reference to the sheet. The sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...

How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:

Debra
Bingo! With BadSheet selected, Insert - Name - Define showed

"BadSheet"

at the far right. What does that mean? When I then clicked on

AllParts,

the reference was to 'All Parts'#REF! (The list AllParts is on sheet

"All

Parts").
Can you please, please, please explain to me what I did to get into this
mess? And what I need to watch out for so that I don't do it again?

Does

it have to do with references while in a With - End With construct? I

ask

that because I was in a With Sheets("All Parts") construct when I named

the

list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...


Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:


Debra
Thanks for your response. I checked both lists and both references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a new

blank


sheet and rebuilt the Data Validation cells but in the new sheet

(without


copying anything from BadSheet). All works well. Does this indicate

to

you


that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
. ..



Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:



Tom
Thanks for your reply. The naming of the list is done by code.

The


code copies/pastes (shorter) lists from several sheets to make one

longer



list. All this is within a For loop because there are two final

lists

to be



setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.


Otto
"Tom Ogilvy" wrote in message
.. .




What does the formula for AllParts (refers to ) look like. Are you

using



relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
...




Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.




I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".




I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I get

the


whole




list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,




and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different

list.


I go to the cell in BadSheet, clear the Data Validation, redo the

Data


Validation to =AllParts. I get the same 14 items from the first

AllParts




list.

Is this sheet corrupted or have I lost it? Thanks for your help.

Otto


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Good chance it was created earlier in the development process and not as a
result of this code.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Debra
RngName is straight text without any reference to the sheet. The
sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...
How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed
"BadSheet"
at the far right. What does that mean? When I then clicked on
AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet
"All
Parts").
Can you please, please, please explain to me what I did to get into
this
mess? And what I need to watch out for so that I don't do it again?
Does
it have to do with references while in a With - End With construct? I
ask
that because I was in a With Sheets("All Parts") construct when I
named
the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the
BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both
references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a
new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this indicate
to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts
shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by code.

The

code copies/pastes (shorter) lists from several sheets to make one

longer


list. All this is within a For loop because there are two final
lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like. Are
you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I
get

the

whole



list

I do it all again in a cell in the sheet where I want it, call it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely different

list.

I go to the cell in BadSheet, clear the Data Validation, redo the

Data

Validation to =AllParts. I get the same 14 items from the first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Tom, Debra
I will do that. One thing I am not sure about and want to be sure about
is this: What is the process I went through or maybe went through to create
this situation?
I still don't know what causes this. Is it something like creating the same
name by two different means? What means? I know that, in the development
process, I created that name manually at first, and then by code after that.
I'm so unsure of what I did that I don't know the questions to ask. Thanks
for your help as always. Otto
"Tom Ogilvy" wrote in message
...
Good chance it was created earlier in the development process and not as a
result of this code.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Debra
RngName is straight text without any reference to the sheet. The
sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All
Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...
How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed
"BadSheet"
at the far right. What does that mean? When I then clicked on
AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on sheet
"All
Parts").
Can you please, please, please explain to me what I did to get into
this
mess? And what I need to watch out for so that I don't do it again?
Does
it have to do with references while in a With - End With construct?
I
ask
that because I was in a With Sheets("All Parts") construct when I
named
the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the
BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both
references
carry the sheet name.
I just renamed the BadSheet something else and then inserted a
new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this
indicate
to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a
workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts
shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by
code.

The

code copies/pastes (shorter) lists from several sheets to make
one

longer


list. All this is within a For loop because there are two final
lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like. Are
you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on
sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I
get

the

whole



list

I do it all again in a cell in the sheet where I want it, call
it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely
different

list.

I go to the cell in BadSheet, clear the Data Validation, redo
the

Data

Validation to =AllParts. I get the same 14 items from the
first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your
help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

By default, range names are workbook level names. If you select a range
of cells, click in the Name box, type a name, and press Enter, it
creates a workbook level name.

To manually create a sheet level name, activate the sheet, and choose
InsertNameDefine. Include the active sheet name in the range name, e.g.

Names in Workbook: BadSheet!AllParts
Refers to: Normal1!$A$1:$A$14


Otto Moehrbach wrote:
Tom, Debra
I will do that. One thing I am not sure about and want to be
sure about
is this: What is the process I went through or maybe went through to
create
this situation?
I still don't know what causes this. Is it something like creating
the same
name by two different means? What means? I know that, in the
development
process, I created that name manually at first, and then by code
after that.
I'm so unsure of what I did that I don't know the questions to ask.
Thanks
for your help as always. Otto
"Tom Ogilvy" wrote in message
...

Good chance it was created earlier in the development process and not
as a
result of this code.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
.. .

Debra
RngName is straight text without any reference to the sheet. The

sheet

is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All

Parts")

construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...

How is RngName defined? If it includes the sheet name, the range name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:

Debra
Bingo! With BadSheet selected, Insert - Name - Define showed

"BadSheet"

at the far right. What does that mean? When I then clicked on

AllParts,

the reference was to 'All Parts'#REF! (The list AllParts is on sheet

"All

Parts").
Can you please, please, please explain to me what I did to get into

this

mess? And what I need to watch out for so that I don't do it again?

Does

it have to do with references while in a With - End With construct?

I

ask

that because I was in a With Sheets("All Parts") construct when I

named

the

list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
. ..


Both references will have the Normal1 sheet name, but if you look at
InsertNameDefine while BadSheet is selected, it may show the

BadSheet

name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:


Debra
Thanks for your response. I checked both lists and both

references

carry the sheet name.
I just renamed the BadSheet something else and then inserted a

new

blank


sheet and rebuilt the Data Validation cells but in the new sheet

(without


copying anything from BadSheet). All works well. Does this

indicate

to

you


that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
. ..



Perhaps you have a sheet level range named AllParts, and a

workbook

level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts

shows

the sheet name in the list of named ranges.

Otto Moehrbach wrote:



Tom
Thanks for your reply. The naming of the list is done by

code.

The


code copies/pastes (shorter) lists from several sheets to make

one

longer



list. All this is within a For loop because there are two final

lists

to be



setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.


Otto
"Tom Ogilvy" wrote in message
.. .




What does the formula for AllParts (refers to ) look like. Are

you

using



relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
bl...




Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on

sheet

Normal1.




I do a Data Validation in a cell in sheet Normal1 with List and

"=AllParts".




I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2. I

get

the


whole




list

I do it all again in a cell in the sheet where I want it, call

it

BadSheet,




and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely

different

list.


I go to the cell in BadSheet, clear the Data Validation, redo

the

Data


Validation to =AllParts. I get the same 14 items from the

first

AllParts




list.

Is this sheet corrupted or have I lost it? Thanks for your

help.

Otto


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html




--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html








--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Activesheet.Names.Add Name:="AllParts", Refersto:="=Sheet2!$A$1:$A$47"

creates a sheet level name.

I think you are worrying about it too much. You know how to check for the
situation.

--
Regards,
Tom Ogilvy



"Otto Moehrbach" wrote in message
...
Tom, Debra
I will do that. One thing I am not sure about and want to be sure
about
is this: What is the process I went through or maybe went through to
create
this situation?
I still don't know what causes this. Is it something like creating the
same
name by two different means? What means? I know that, in the development
process, I created that name manually at first, and then by code after
that.
I'm so unsure of what I did that I don't know the questions to ask.
Thanks
for your help as always. Otto
"Tom Ogilvy" wrote in message
...
Good chance it was created earlier in the development process and not as
a
result of this code.

--
Regards,
Tom Ogilvy

"Otto Moehrbach" wrote in message
...
Debra
RngName is straight text without any reference to the sheet. The
sheet
is "All Parts".

RngName = "AllParts"
Set Rng = .....................
Rng.Name = RngName

Note that RngName is defined as above BEFORE the With Sheets("All
Parts")
construct.
Thanks for taking the time on a Sunday to help me with this. Otto
"Debra Dalgleish" wrote in message
...
How is RngName defined? If it includes the sheet name, the range
name
will be sheet level, For example: 'All Parts'!AllParts

Otto Moehrbach wrote:
Debra
Bingo! With BadSheet selected, Insert - Name - Define showed
"BadSheet"
at the far right. What does that mean? When I then clicked on
AllParts,
the reference was to 'All Parts'#REF! (The list AllParts is on
sheet
"All
Parts").
Can you please, please, please explain to me what I did to get
into
this
mess? And what I need to watch out for so that I don't do it
again?
Does
it have to do with references while in a With - End With
construct?
I
ask
that because I was in a With Sheets("All Parts") construct when I
named
the
list. The code is:

Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Sort Key1:=.Range("A1"), Order1:=xlAscending, _
Header:=xlNo
Set Rng = .Range("A1", .Range("A" & Rows.Count).End(xlUp))
Rng.Name = RngName

Thanks for your help. Otto
"Debra Dalgleish" wrote in message
...

Both references will have the Normal1 sheet name, but if you look
at
InsertNameDefine while BadSheet is selected, it may show the
BadSheet
name at the right side of the Names list:

AllParts BadSheet

Or, while on BadSheet, select a cell in an empty column, and
choose
InsertNamePaste. Select AllParts, and click PasteList.
See what range is listed for AllParts.

Otto Moehrbach wrote:

Debra
Thanks for your response. I checked both lists and both
references
carry the sheet name.
I just renamed the BadSheet something else and then inserted
a
new

blank

sheet and rebuilt the Data Validation cells but in the new sheet

(without

copying anything from BadSheet). All works well. Does this
indicate
to

you

that BadSheet is corrupted? Thanks again. Otto
"Debra Dalgleish" wrote in message
...


Perhaps you have a sheet level range named AllParts, and a
workbook
level range named AllParts.

Select BadSheet, choose InsertNameDefine, and see if AllParts
shows
the sheet name in the list of named ranges.

Otto Moehrbach wrote:


Tom
Thanks for your reply. The naming of the list is done by
code.

The

code copies/pastes (shorter) lists from several sheets to make
one

longer


list. All this is within a For loop because there are two
final
lists

to be


setup and named.
Rng.Name = RngName
Looking at Insert - Name - select "AllParts", the reference is

absolute.

Otto
"Tom Ogilvy" wrote in message
...



What does the formula for AllParts (refers to ) look like.
Are
you

using


relative references? they should be absolute.


Refersto: =Normal1!$A$1:$A$47

--
Regards,
Tom Ogilvy


Otto Moehrbach wrote in message
.. .



Excel 2003, WinXP
I have one list (column), named AllParts, with 47 items on
sheet

Normal1.



I do a Data Validation in a cell in sheet Normal1 with List
and

"=AllParts".



I get the whole list.

I do Data Validation, the same, in a cell in sheet Normal2.
I
get

the

whole



list

I do it all again in a cell in the sheet where I want it,
call
it

BadSheet,



and all I get is the first 14 items from the list.

I reset the range name AllParts to another, completely
different

list.

I go to the cell in BadSheet, clear the Data Validation, redo
the

Data

Validation to =AllParts. I get the same 14 items from the
first

AllParts



list.

Is this sheet corrupted or have I lost it? Thanks for your
help.

Otto



--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html






--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html