2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Protecting Formula Columns While Copy-Pasting Rows

The Situation

I have a workbook with assignments with a sheet for each month with data in the columns A to Z. This workbook is used by several different teams to view data about these assignments, with a set of columns for each team.

In order to automate data entry, two columns, P and V, contain formulae that pull from cells in the same row the formula is in. This is necessary so each team can easily find the data they need. Yes, I do need the sheets to be this foolproof. Columns P and V are protected, the rest aren't.

The Problem

It's often necessary to cut and paste data from one month to another or to move rows up or down, without overwriting the formulae columns. Because of the way the sheets are structured, I can't just copy an entire row and insert it where I need it to be; the protected cells keep me from doing that. So either the formulae get overwritten or I have to move data in three steps to work around the protection.

What I've Tried

I thought hiding the formulae would protect them, but sadly they can still be overwritten. I've checked the protection settings, and I've searched this sub for solutions. Maybe I'm just not seeing it, maybe it's not intuitively named, but I haven't found a solution yet.

If the only solution is to create multiple workbooks, one for each team, and use formulae, PQ or what have you to copy the data, then so be it. I'll have to set up a new workbook for next year anyway, so it's a good time to do so. But if possible I'd like to keep the workbook as-is.

Edit: I forgot to mention an important detail: In order to keep conditional formatting intact, I only paste values, which excludes formulae, thus overwriting the formula with whatever value was in the original cell.

I'm starting to realise that the workbook may be a bit bloated...

submitted by /u/PrimeResponse
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#rows.com
#Excel
#Formula
#Worksheet
#Workbook
#Copy-Paste
#Row
#Column
#Protected Cells
#Formulae
#Conditional Formatting
#Data Entry
#Paste Values
#Data Overwrite
#PQ
#Automation
#Teams
#Assignments
#Monthly Data
#Cell Protection
Protecting Formula Columns While Copy-Pasting Rows