Protecting formula only

[dtherrian]

Hi Excel Experts. I have a question it maybe a very simple answer and I am just blanking on the process. I have a spreadsheet that I have create with my master formulas created in the fields. It has taken me a very long time and I am extremely proud of the work that I have created in this spreadsheet. It was a very complex problem. I actually still have one more part to figure out I just do not have a lot of free time on my job to work on it so I do it when I can.

Anyway here is my problem. The formulas are in the spreadsheet but what I put into that spreadsheet varies and changes all the time. Is there a way to copy my text into the spreadsheet without losing my formulas? I thought that creating it as a template was the answer, but that didn't exactly help. Please advise. I would love to show you all the work I have done on this because it is the complex problem I posted last year.. funny it just suddenly came to me. I need to make up a dummy one to show you all those since it is very confidential information. Please help!!!

 

[Luke M]

This will be tough to answer w/o more info about the layout, or what it is exactly you are wanting to do. Are you trying to copy the text over cells with formulas, or are you just worried that copying a large block of cells into your tool will erase cells?

Generally speaking, this problem is avoided by segreating your information. I try to have 1 sheet which has all my inputs, 1 sheet for my heavy calculations, and 1 sheet for the outputs. The calculation sheet can be hidden/protected then. My output sheet sometimes has interactive capabilities, so I can't have it in full protection, but I can place most of the controls at top away from my output cells, and/or if the output cells get erased, they are easier to repair as it's generally some linking cell, like:
=DataCell

Then, your input page, Users can type/paste/scan whatever they want. Depending on what you're doing, you can either build a strong enough tool that can handle various formats, or try to force users to put things in correct spots.

Does that help?

 

[NARAYANK991]

Hi ,

You say : Is there a way to copy my text into the spreadsheet without losing my formulas?

You have not specified where it is you are copying from ?

Suppose your data is in cells spread randomly all over the workbook e.g. say E7 , J17 , H33 , AB11 ,.... ; now , the formulae in your workbook will be using the data in these cells to do whatever you want the workbook to do.

In case you wish to copy text into your spreadsheet , the simplest way is to dedicate a separate worksheet to the input data ; just copy paste all input data into this worksheet , in any column , row , matrix.

Now , in the cells which had text in them earlier , such as E7 , J17 , H33 , AB11 ,... put in formulae to refer to the proper cells in the input data tab e.g. E7 can contain a formula such as =A3 ; J17 can have a formula such as A32 , and so on. Your existing worksheet remains untouched except for the addition of the formulae in cells which earlier had input data in them.

Narayan