Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
data validation using named range from another file | Excel Worksheet Functions | |||
Data Validation List from a Dynamic Named Range on Another Workshe | Excel Worksheet Functions | |||
Data Validation drop-down width, with named range source (XL03 and | Excel Worksheet Functions | |||
Using a data validation list to look up a defined named range in another worksheet | Charts and Charting in Excel | |||
Data validation named range update | Excel Discussion (Misc queries) |