Forum Discussion
Solved
Prevent a single protected cell from being copied over in the ROW?
I am trying to Copy/Paste_Values in a ROW where only one cell is protected (and that cell needs to retain its formula). I get an error that a "cell is protected..." and none of the other cells in th...
- I don't think so. to copy to a new range all the cells in that new range must be unlocked. you could come up with some macro solution to either create a function that copies cell by cell and then ignore the protected cell(s) or something.
I suggest just re-arrange the sheet accordingly or create a data range and a constant/locked range and then create an 'output' range that "copies" the data into the format you want. For example A, B, D, E should copy but C is fixed then in M, N, O, P you have the "copy" ranges and in Q is fixed and formulas in A:E are =M, =N, =Q, =O, =P (notice how I inserted =Q in the middle)
so then you can copy down in columns M:P and col Q is locked but in columns A:E it shows the 'copy' values on either side of the fixed C value.
I think I get that basic idea but from the sounds of it you have a lot of lookup values that you are using INDIRECT() to do and I'm hoping we could help you restructure your sheet and formulas to make it more user friendly.
As for upload, you can put it on sharepoint, onedrive, dropbox or the like and share a link or you can PM me and attach it there (click on my name/pfp and then click message)
As for upload, you can put it on sharepoint, onedrive, dropbox or the like and share a link or you can PM me and attach it there (click on my name/pfp and then click message)
Thanks again for your help. Using INDIRECT is the reason I don't want to reorder the columns, but I don't think this will make my current problem any less of a problem. I was really hoping that there was a way to do this by circumventing the protection scheme - something not so rigid.
- I really think it might be helpful to you to have us look at your sheet structure and formulas. because INDIRECT is a volatile function meaning your sheet is inefficient and the nature of it can prevent ease of expansion and usability. considering this is all free advice here it seems like low cost and high potential return but of course it is your choice.