Lock work sheet formulas while pasting new data 2

[PeanutB7]

Dear Incredibly Helpfull People,

I am trying to lock all formulas in a complicated worksheet as a template while pasting new data in for various regions multiple times. Is there a way to protect all formulas within a worksheet but allow to overlay new data without overiding the needed formulas. Because of the complexity of the worksheet I can not cut and paste the data in between the given formulas. I must be able to overlay the data to intermingle with the locked formulas.

Please help!

Thank you in advance,

JB

 

[SkipVought]

Hi,

Could you give a simple example that would clearly and succinctly define the problem?

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[PeanutB7]

Absolutely Skip,

Most formulas used within the worksheet are additions or sums.

For example I may have a column of seven numbers summed at the bottom of the column.

234
345
344
234
256
5675
4544
______
=sum(b1:b7)

The =sum(b1:B7) is what I need to not overwrite when inserting new data from different regions. There are thirty regions total. I have the first region worksheet functioning correctly and wish to freeze all formulas while inserting new data only.

The columnlar math functions would happen fifteen times in the same column, within the worksheet, and in six different columns as well. Therefore placing in the formulas would be difficult each time. My data is set up in the exact layout as the finished worksheet but does not include all of the required formulas. What I would like to do is paste my data (numbers to be totaled) into the primary worksheet without deleting the =sum(b1:b7) that is required.

There probably is some simple manner to accomplish this task but regretfully I am not aware of it and have tried all help functions to no avail. Any help would be greatly appreciated.

Thanks Skip,

JB

 

[Lilliabeth]

Maybe this is what you want?

Select the cells that do not have fomulas and unlock them so they will be unaffected by sheet protection:

Edit > Go To
Special button
Constants
Right-click on one of the selected cells and choose Format Cells
Protection tab
Uncheck Locked
Deselect and then protect the sheet

 

[PeanutB7]

Thanks Lilliabeth,

I did try locking and protecting the formula cells (while leaving the data cells unlocked) and pasting the data over top. The protected locked cells will not allow the paste of the data over top. If you paste data above the formula it works fine but if you paste data with multiple areas of formulas within the worksheet the paste will not be allowed.

What I need is to lock the formulas in the sheet while allowing the data only to change.

Thanks though,

JB

 

[AnotherHiggins]

My data is set up in the [attn]exact layout[/attn] as the finished worksheet but does not include all of the required formulas.

Do you really mean exact? Including the blank rows where the formulas should be?

If so, then just copy, select the top of the range where you wantto paste and go to Edit > Paste Special and tick the box beside Skip Blanks.

Voilà!

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[PeanutB7]

Thanks John,

I thought it may be something simple but not quite that simple. I tried it and it works beautifully - Thank You Kindly!

JB

 

[SkipVought]

"numbers summed at the bottom of the column..."

You'd save yourself ALOT of problems if your agregations were ALWAYS IN THE SAME PLACE. Why put totals at the bottom??? That's a throwback to paper, pencil and adding machine sheet-by-sheet reconcilliation.

I almost always put my totals at the VERY TOP. That way, I know exactly where they are, the user see's the aggregations immediately without having to HUNT for the "bottom line".

Skip,

_{[red]Be Advised![/red] A man who jumps from a bridge in Paris, is…
INSANE!}

 

[PeanutB7]

Thanks Skip,

The Format of the sheet is an actual balance sheet design with sub totals and totals. The layout is not flexible.

Thanks for the input and assistance,

JB