I have a worksheet with named lists. I have a second worksheet that has
validation references (=listname) to those lists to create dropdown lists.
This is working fine. I have now distributed this spreadsheet to multiple
users.

I want to now update one or more lists in my master file and re-distribute
the list worksheet and give the users a method to replace the lists worksheet
in their local copy.

What is happening is that when I copy the updated list to the local
worksheet, and delete the original list worksheet, the second worksheet no
longer sees the named lists. The same thing happens if I first delete the
list worksheet and then copy the new list worksheet. The lists are still
correctly visable in the lists worksheet.

How do I get the second worksheet to see the named lists again?

Hi

Assuming the users are capable, the easiest way would be to tell them to
click of the small grey cell to the left of A and above Row 1 of the new
sheet. This will select all of the cells.
Copy
Move to cell A1 of the existing second sheet and Paste.
This will replace all of the named ranges with the new values.

--
Regards

Roger Govier


"DocBrown" wrote in message
...
I have a worksheet with named lists. I have a second worksheet that has
validation references (=listname) to those lists to create dropdown
lists.
This is working fine. I have now distributed this spreadsheet to
multiple
users.

I want to now update one or more lists in my master file and
re-distribute
the list worksheet and give the users a method to replace the lists
worksheet
in their local copy.

What is happening is that when I copy the updated list to the local
worksheet, and delete the original list worksheet, the second
worksheet no
longer sees the named lists. The same thing happens if I first delete
the
list worksheet and then copy the new list worksheet. The lists are
still
correctly visable in the lists worksheet.

How do I get the second worksheet to see the named lists again?

Copying the cells doesn't quite work if the number of items in the list
changes.

Here's some details...
I have two worksheets. One called 'Lists' which contains the named lists.
The second worksheet, called 'Template', has validation that references to
the named lists to create drop down menus.

In my workbook, the data in the Define Name window (Insert-Name-Define) is
in sync between the two worksheets, but after I replace the 'Lists' worksheet
in the local copy of the spreadsheet, the entry in the Define Name window is
no longer correct in the template worksheet. In the template worksheet, the
'Refers to:' changes from:

=OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A:$A),1)

to:

=OFFSET(#REF!$A$5,0,0,COUNTA(#REF!$A:$A),1)

If I manually change the #REF to Lists, the drop down works again. So the
link between worksheets is lost if I delete the original 'Lists' worksheet.

How can I prevent this from happening? Is there another way to replace the
'Lists' worksheet without breaking the links while at the same time updating
the named lists?

Thanks,
John S.


"Roger Govier" wrote:

Hi

Assuming the users are capable, the easiest way would be to tell them to
click of the small grey cell to the left of A and above Row 1 of the new
sheet. This will select all of the cells.
Copy
Move to cell A1 of the existing second sheet and Paste.
This will replace all of the named ranges with the new values.

--
Regards

Roger Govier


"DocBrown" wrote in message
...
I have a worksheet with named lists. I have a second worksheet that has
validation references (=listname) to those lists to create dropdown
lists.
This is working fine. I have now distributed this spreadsheet to
multiple
users.

I want to now update one or more lists in my master file and
re-distribute
the list worksheet and give the users a method to replace the lists
worksheet
in their local copy.

What is happening is that when I copy the updated list to the local
worksheet, and delete the original list worksheet, the second
worksheet no
longer sees the named lists. The same thing happens if I first delete
the
list worksheet and then copy the new list worksheet. The lists are
still
correctly visable in the lists worksheet.

How do I get the second worksheet to see the named lists again?

Hi John

I was not suggesting "replacing" the sheet - that would give rise to
errors.
What I said was to copy the data (all of it) from the new sheet, and
then Paste to Cells A1 of the sheet Lists.
All of the names, both their quantity and values will be replaced on the
sheet, but none of the formulae will have altered.

Works fine for me in XL2000,2002,2003 and 2007.

--
Regards

Roger Govier


"DocBrown" wrote in message
...
Copying the cells doesn't quite work if the number of items in the
list
changes.

Here's some details...
I have two worksheets. One called 'Lists' which contains the named
lists.
The second worksheet, called 'Template', has validation that
references to
the named lists to create drop down menus.

In my workbook, the data in the Define Name window
(Insert-Name-Define) is
in sync between the two worksheets, but after I replace the 'Lists'
worksheet
in the local copy of the spreadsheet, the entry in the Define Name
window is
no longer correct in the template worksheet. In the template
worksheet, the
'Refers to:' changes from:

=OFFSET(Lists!$A$5,0,0,COUNTA(Lists!$A:$A),1)

to:

=OFFSET(#REF!$A$5,0,0,COUNTA(#REF!$A:$A),1)

If I manually change the #REF to Lists, the drop down works again. So
the
link between worksheets is lost if I delete the original 'Lists'
worksheet.

How can I prevent this from happening? Is there another way to replace
the
'Lists' worksheet without breaking the links while at the same time
updating
the named lists?

Thanks,
John S.


"Roger Govier" wrote:

Hi

Assuming the users are capable, the easiest way would be to tell them
to
click of the small grey cell to the left of A and above Row 1 of the
new
sheet. This will select all of the cells.
Copy
Move to cell A1 of the existing second sheet and Paste.
This will replace all of the named ranges with the new values.

--
Regards

Roger Govier


"DocBrown" wrote in message
...
I have a worksheet with named lists. I have a second worksheet that
has
validation references (=listname) to those lists to create dropdown
lists.
This is working fine. I have now distributed this spreadsheet to
multiple
users.

I want to now update one or more lists in my master file and
re-distribute
the list worksheet and give the users a method to replace the lists
worksheet
in their local copy.

What is happening is that when I copy the updated list to the local
worksheet, and delete the original list worksheet, the second
worksheet no
longer sees the named lists. The same thing happens if I first
delete
the
list worksheet and then copy the new list worksheet. The lists are
still
correctly visable in the lists worksheet.

How do I get the second worksheet to see the named lists again?