Data validation named range update
 

I delete a sheet containing named ranges with data in column A that are used
for data validation on another sheet in the workbook. I copy a sheet into
the workbook that contains the same names with revised ranges and values -
some with additional values - some additional named ranges. The lists no
longer show up in the validated cells on the main sheet. If I click in a
validated cell and check the defined name, it equates to =#REF!$A$1:$A$9.
The revised named range is actually larger.

Is there a way to programmatically update the references on the main sheet
to reflect the revisions on the data sheet?

--
Phil

Data validation named range update
 

Maybe you could use =indirect().

I used a worksheet named Sheet4.

I added a range name to sheet4 named myList.

Then I added a name that pointed at that name:

myValList
and pointed to:
=INDIRECT("mylist")

When I deleted sheet4 and readded it, my data|validation lists worked ok.



Phil Deem wrote:

I delete a sheet containing named ranges with data in column A that are used
for data validation on another sheet in the workbook. I copy a sheet into
the workbook that contains the same names with revised ranges and values -
some with additional values - some additional named ranges. The lists no
longer show up in the validated cells on the main sheet. If I click in a
validated cell and check the defined name, it equates to =#REF!$A$1:$A$9.
The revised named range is actually larger.

Is there a way to programmatically update the references on the main sheet
to reflect the revisions on the data sheet?

--
Phil

--

Dave Peterson

Data validation named range update
 

Thanks, Dave. That works for me too.
--
Phil


"Dave Peterson" wrote:

Maybe you could use =indirect().

I used a worksheet named Sheet4.

I added a range name to sheet4 named myList.

Then I added a name that pointed at that name:

myValList
and pointed to:
=INDIRECT("mylist")

When I deleted sheet4 and readded it, my data|validation lists worked ok.



Phil Deem wrote:

I delete a sheet containing named ranges with data in column A that are used
for data validation on another sheet in the workbook. I copy a sheet into
the workbook that contains the same names with revised ranges and values -
some with additional values - some additional named ranges. The lists no
longer show up in the validated cells on the main sheet. If I click in a
validated cell and check the defined name, it equates to =#REF!$A$1:$A$9.
The revised named range is actually larger.

Is there a way to programmatically update the references on the main sheet
to reflect the revisions on the data sheet?

--
Phil

--

Dave Peterson