Replacing Data Validation references

[lyambor]

I know how to clear data validation in a range of cells or change the list reference
But I have over 600 cells that need changing so even copying is a bit arduous

What I would like to do is

replace my list
=listname

with a different
=differentlistname

so I don't have to go into 20 different columns to make the changes


I am basically copying a master sheet but changing formulas based on subject for example art to science -
this replace works great for formulas replacing art with science but not for data validation list references

Any thoughts?
Thank you

 

[Fazza]

I'm not clear on exactly what is wanted. So not sure if this helps or not: you can select one cell that has validation using listname and via special cells (CTRL-G or F5, then ALT-S or special, v for validation, and then e for same) select all the cells with similar data validation. Then you can edit them all at once to change them to differentlistname. OK?

 

[lyambor]

Thank you will try when back in office
Did not see where I could upload sample file

 

[Fazza]

Sample files can't be uploaded. regards

 

[lyambor]

I appreciate you taking the time - However your method will not work for me
There are too many cells with similar data validation to select

I am trying to have a data validation list changed throughout an entire worksheet automatically with a global replace

For example in 20 column /going over 100 rows I have assigned a data validation drop down referencing a list called =tart
Now I want all these cells to use a different drop down list called =tmedia

I was hoping there would be a fast way to replace =tart with =tmedia without having to go to the cells

Thank you
Leslie

 

[Fazza]

Please can you explain how what I proposed does not do what is wanted? Thanks

 

[lyambor]

Your keystrokes took me to the actual dropdown list of names
I am trying to edit the reference to the list

Data > data Validation > List
My list =tart is in 21 columns of 800 rows each
in a sheet called ART

I copied the art sheet and renamed it
SCIENCE
I made a list called =tscience

So what I wanted to do in the science sheet was quickly replace the data validations references from =tart to =science
without going to all the columns
a quick replace of lists =tart with =tscience in data > data validation > list

I have moved on and just ended up manually making the changes copying the new reference down the columns
When I have time I will try a Macro

Many thanks

 

[Fazza]

Appreciate the explanation, thank you. There is some simple misunderstanding I think.

The keystrokes I described doesn't take you to the actual dropdown list of names. It selects all the cells with the same data validation as the cell you started on. Say, all the cells that use data validation =tart

The next step is to change the data validation (whilst that selection is unchanged, which might be in 21 columns & 800 rows). So ALT-D-L and then change to =tscience

OK?

If that is not what is wanted I can code what is wanted if I understand what is required.