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.
Thanks for the detailed reply. Your solution was the same as what I have been doing, but manually. My spreadsheet has evolved over several years and contains 68 columns together with many related sheets. But the real snag is I would have to update all the formulas where I used INDIRECT, which are many. So changing the column order is not really an option for now.
I was even hoping someone had a Power Tool addon they could recommend (I have Kutools but it doesn't do what I need).
Anyway, thanks again.
I was even hoping someone had a Power Tool addon they could recommend (I have Kutools but it doesn't do what I need).
Anyway, thanks again.
my recommendation is to NOT use INDIRECT unless absolutely necessary. If you share a sample of the sheet and what you are trying to do, in nearly all cases we can find a way to do it without INDIRECT or at least minimize its use (e.g. only to a few defined 'names'
- I appreciate your offer but without the ability to upload, I can only give you a desc of the issue.
Each day is in consecutive rows and has formulas in each cell which are fed from various other sheets except one cell, say in today's row, cell E433 which has a formula +F434. At the end of each day, I need to COPY/PASTE_VALUES in the entire row except for E433, as it won't have a value in it until the next day, which will be reflected in entries for ROW 434.
Although I used one cell above, the actual s/s has several of these cells in each row.- 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)- 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.