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