pasting row to another workbook prompts because same defined name exists in new book

N

neowok

I have a set of weekly spreadsheets, all the same except for the dat
that is entered onto them.

What we ned to do is cut and paste lines from last weeks workbook int
this weeks one. The problem is I have set up data validation on one o
the columns based on a list of names, and so now it prompts ever singl
time we try to paste into the new workbook, because the same define
name 'tradelist' is in the new sheet.

is there any way I can stop this prompt somehow because tis driving u
mad and taking up time clicking yes or not (when it makes no differenc
if we click yes OR no because its exactly the same list of names i
both workbooks). Its also confusing people because they dont know i
they click yes or not.

There must be some way around this
 
N

neowok

anyone? there must be some way around this. Because the list source fo
data validation cannot go on another sheet (which is just stupid), I'v
had to created the list in cells on a hidden sheet, then define a nam
to refer to those cells and use this name as the data validation sourc
for the list.

There must be some way to either stop it prompting due to that nam
being in the destination sheet when pasting, or to remove the define
name from the equation completely and somehow get this list into th
data validation another way (by code maybe?)
 
N

neowok

ok i assume there is no way around it then, and we are going to be stuc
being prompted for every single line thats copied and pasted into th
next weeks sheet
 
D

Dave Peterson

I'm not sure I understand what you want.

If you can just drop the Data|Validation from the pasted cells, maybe just
Paste|special|values would work for you (and reapply Data|validation later if
you need to).
 
N

neowok

we have weekly sheets with about 800 rows of data and 36 columns entre
for the week for various engineering jobs. The next week most of th
same jobs are still running with most of the dame details so rathe
than trying to type it all out we have to cut and paste it from th
previous weeks sheet and then change the releant columns to reflec
figures for this week.

The data validation column is for trades and these are one of the mai
things that might change in the next week, so we need the dat
validation to stop people entering wrong or different trades from thos
we specify, and also because graphs will be produced on this we need t
have all the names spelt identically etc

I dont think there is any way around it, its rediculous that you can
set data validation on a range of cells on a different sheet becaus
then i wouldnt have to define a name at all and wouldnt be getting thi
prompt, i could just put the list in my hidden sheet (which is what iv
done anyway but had to define a name to point at it).

the only other option i see is somehow putting this list on the mai
data sheet where the cutting and pasting is being done, then i won
need to define a name. the problem is this sheet has rows added an
overwritten al the time so it looks like the only option would be t
stick the list along row A next to the title and maybe turn the tex
white or something to hide it
 
Top