Questions

Excel

+
0 Votes
Locked

Excel

thurmbo
Hi, I am trying to protect specific cells that contain formulas, but also need to allow others to cut/paste rows (that have protected cells) to other sections of the sheet.

I have tried to protect the sheet using all the of checkbox configurations, but can't seem to find a way to allow cut/paste rows that contain individually protected cells.

Does anyone know a way to do this? Much appreciated.
  • +
    0 Votes
    LocoLobo

    Do you want to move the protected cells to another location?

    Would Copy -> Paste work? Copy will work and in Excel 2003 you can allow inserting rows and columns. Nothing I tried worked either. Which is good for me. We don't want our users moving protected cells around.

    Maybe someone else here can help :)

    +
    0 Votes
    thurmbo

    Yes, I have a need for my clients to be able to copy/paste additional Rows (of a templated Row) so they can log in specific data. However, there are locked cells within that Row to prevent them from altering Formulas. So they are unable to Copy & Paste unless I keep the sheet unprotected, which defeats my purpose.

    Thank you,

    +
    0 Votes

    OK

    LocoLobo

    So far Toms45 has the best idea. The problem with that is your cells referencing the formulas can't be protected. Or at least that's my understanding.

    In my trials yesterday with Excel 2003 you can allow "Insert Rows / Insert Columns", but it had to be the whole row/column. I was unable to "Insert copied cells". Maybe MS will have that available in Office 20XX?

    Good Luck

    +
    0 Votes
    thurmbo

    Unfortunately I am unable to keep cells locked (regardless if a formula resides in the cell) in fear that clients will type in the wrong cells. I realized I still have cells that need to be locked so the tab key will skip over them.

    I thank you for your trials & troubleshooting in regards to my dilemna!

    Much appreciated

    +
    0 Votes
    toms45

    Try putting your formulas way down in row 3000 column az in a named range, then reference the named range in the calculated cells. Seems like I remember something like this from about 10 years ago.

    +
    0 Votes
    thurmbo

    Thank you, I still could not get it to work properly as I had hoped to, but your suggestion taught me something new that I can apply to other areas of my spreadsheet.

    Much appreciated!

    +
    0 Votes
    liz

    I have a similar problem, and wondered if you ever got your problem solved?

    +
    0 Votes
    thurmbo

    Hi Liz, I have not been able to get this issue resolved, but will be looking at fwang's helpful hints.

    Thank you fwang!

    +
    0 Votes
    liz

    As he very simply suggested, I "wrote" the macro to include the unlocking of cells prior to adding rows, then locked cells to finish off the macro. It worked beautifully.

    I still want to fine tune the macro so that it is able to properly add a row and replicate formulas for one row after another. I will us the Goto and Offset function for that.

    +
    0 Votes
    fwang

    1) Give user password (or protect sheet without password), unprotect sheet, then do cut/paste, after that, protect sheet again.

    2) Do not put any formula, after user finishes all the cut/paste job. Run a macro to generate all the formula. You can also use macro to protect the final sheet and lock it.

    +
    0 Votes
    liz

    The formulas are already all there.

    Are you suggesting that I can add into the macro to first unprotect the sheet, do the add rows sequence, then protect the sheet?

    If I can do that, I think that will solve the problem.

    Thanks!

  • +
    0 Votes
    LocoLobo

    Do you want to move the protected cells to another location?

    Would Copy -> Paste work? Copy will work and in Excel 2003 you can allow inserting rows and columns. Nothing I tried worked either. Which is good for me. We don't want our users moving protected cells around.

    Maybe someone else here can help :)

    +
    0 Votes
    thurmbo

    Yes, I have a need for my clients to be able to copy/paste additional Rows (of a templated Row) so they can log in specific data. However, there are locked cells within that Row to prevent them from altering Formulas. So they are unable to Copy & Paste unless I keep the sheet unprotected, which defeats my purpose.

    Thank you,

    +
    0 Votes

    OK

    LocoLobo

    So far Toms45 has the best idea. The problem with that is your cells referencing the formulas can't be protected. Or at least that's my understanding.

    In my trials yesterday with Excel 2003 you can allow "Insert Rows / Insert Columns", but it had to be the whole row/column. I was unable to "Insert copied cells". Maybe MS will have that available in Office 20XX?

    Good Luck

    +
    0 Votes
    thurmbo

    Unfortunately I am unable to keep cells locked (regardless if a formula resides in the cell) in fear that clients will type in the wrong cells. I realized I still have cells that need to be locked so the tab key will skip over them.

    I thank you for your trials & troubleshooting in regards to my dilemna!

    Much appreciated

    +
    0 Votes
    toms45

    Try putting your formulas way down in row 3000 column az in a named range, then reference the named range in the calculated cells. Seems like I remember something like this from about 10 years ago.

    +
    0 Votes
    thurmbo

    Thank you, I still could not get it to work properly as I had hoped to, but your suggestion taught me something new that I can apply to other areas of my spreadsheet.

    Much appreciated!

    +
    0 Votes
    liz

    I have a similar problem, and wondered if you ever got your problem solved?

    +
    0 Votes
    thurmbo

    Hi Liz, I have not been able to get this issue resolved, but will be looking at fwang's helpful hints.

    Thank you fwang!

    +
    0 Votes
    liz

    As he very simply suggested, I "wrote" the macro to include the unlocking of cells prior to adding rows, then locked cells to finish off the macro. It worked beautifully.

    I still want to fine tune the macro so that it is able to properly add a row and replicate formulas for one row after another. I will us the Goto and Offset function for that.

    +
    0 Votes
    fwang

    1) Give user password (or protect sheet without password), unprotect sheet, then do cut/paste, after that, protect sheet again.

    2) Do not put any formula, after user finishes all the cut/paste job. Run a macro to generate all the formula. You can also use macro to protect the final sheet and lock it.

    +
    0 Votes
    liz

    The formulas are already all there.

    Are you suggesting that I can add into the macro to first unprotect the sheet, do the add rows sequence, then protect the sheet?

    If I can do that, I think that will solve the problem.

    Thanks!