+ Reply to Thread
Results 1 to 2 of 2

Using Data Validation to protect formats/formulas in cells

  1. #1
    Forum Contributor
    Join Date
    02-20-2010
    Location
    Youngstown, Ohio
    MS-Off Ver
    Excel 2010
    Posts
    181

    Using Data Validation to protect formats/formulas in cells

    I found through some research that I could use the Data Validation command to protect cells from being change if they have formulas in them. I've created a rather complicated (at least I think so) spreadsheet that other people in my office will have their own copy to keep weekly project status reports. There are several cells that run formulas both across the rows and the columns. These cells all have blue text in them to signify that the information is in fact a formula and does NOT require any input. All other cells are in black and can/should be updated accordingly to produce the end result. The problem is that too many people still use Excel like a piece of graph paper and want to update the formula cells anyway, using such things as calculators, etc. I know, it's ridiculous. So I use the Data Validation>Allow>Custom>Formula and then input the formula for that cell without the "=". If I use the equals function in the Data Validation Formula, the cell can still be modified, but without it cannot. This is great except that when I have a formula such as "=SUM(F6:F28)-F6" and then copy and add an additional 10 rows within this range, the formula does NOT update to a range of F6:F38. Additionally, if I were to copy cell F7 that contains the Data Validated formula of "D7+E7" and paste it in the subsequent cells below it, the formula stays the same all the way down the rows. Clearly, not a good solution. It's only when the "=" function is left in the formula that the Data Validation updates automatically. But again, this leaves the cell vulnerable to changes from unskilled users.

    Is there a way to both protect the cells (I've already gone through the Protect Cells/Protect Sheet option but that require macros, etc) but yet still allow insertion of additional rows as more lines of information is required?

    Thanks in advance,
    Jim

  2. #2
    Forum Contributor
    Join Date
    10-11-2013
    Location
    Austria
    MS-Off Ver
    Excel 2010
    Posts
    136

    Re: Using Data Validation to protect formats/formulas in cells

    Hey Jim,

    I stumbled upon your post while looking for a solution to the same problem you have.
    I know this is an old post and maybe you've already found a solution that works for you, but I came across this page that seems to work for me:
    Bettersolutions.com

    Data Validation - Custom - Formula: =""

    As long as the formula is already in the cell, you can't overwrite it with a new one. You can however pull the formula down the column, and copy and insert rows.
    Like I said, works for me. If you're still hunting for a solution after almost 3 years, could be worth a shot!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1