Something changed my Data Validation lists. Is Search and Substitute Possible?

Hello PPl

i have an workbook that i work with but today when i arrived at work and loaded it all the data lists changed.
In fact they are correct but something added an named sheet , in this case :

=Relatório_Sintese!$R$6:$R$8

when it sould be

=$R$6:$R$8

imagine an entire workbook with this , and the trouble to change all of the different data validation fields....

How can i do this ? i am desperate here trying to search the web and forums and no problem like this found.

pleasseeeeee....

thx in advanced

p.s. i also am trying to ask if an vba macro is possible to resolve the problem
http://www.excelforum.com/excel-prog...-workbook.html

Hello homasa,

is the range =$R$6:$R$8 in the Sheet Relatório_Sintese? In that case, there's nothing to worry about. It's just a different way of addressing the range.

If you want to avoid that, create a range name for data validation. Insert - Name - Define - enter a name, for example DataTable1 and select the range it shall apply to. Then, in the data validation list definition enter

=DataTable1

This way, the list source will always appear the same and the range definition takes care of the data source.

cheers

since the other thread has not received any answers, I closed it and recommend continuing in this one. This was less work than merging the threads.

Sorry, but your workbook is brimming with macros and I won't possibly go through all these to see if they may be the culprit. I suspect there is something with the macros that's causing the problem.

I won't touch this with a barge stick, sorry. I don't speak Portuguese and I can't understand the messages you pop up. I don't know what the column titles or sheet names mean and I can't make sense of either the sheets nor the macros.

well as i am trying to figure this out i get an message for trying to ....

when i record what i wanna this comes out

Please Login or Register  to view this content.

i just cant figure out how to locate range of validation something like

Please Login or Register  to view this content.

how can i do this ?

homasa, if you continue to post in your own thread, it will only result in showing many answers to the thread, thus reducing the probability that members will answer. The less posts to a thread, the more likely you'll receive fresh insights from another member.