Allow for partial deletion of row in protected sheet

honkin

Active Member
Joined
Mar 20, 2012
Messages
385
Office Version
  1. 2016
Platform
  1. MacOS
I have a workbook with multiple sheets that I need to be able to share with someone who will take over doing the results, but I am faced with a dilemma and hope there is a solution.

So each sheet has 27 columns of data which are able to be edited and there are new selections added daily. The 25 columns to the right of these are all P&L calculations, peak equity and drawdown and other such calculations which need to be automated. I have created a formula which will help for automation for this and have locked all of those cells so when the sheet is protected, that part is fully automated. It's pretty simple; I just added to the existing calculation formula so it does nothing if the cell in AA is blank

=IF(AA1467<>"",IF(AND(AA1467>1,Y1467<2),-100,IF(AND(AA1467=1,Y1467<2),(Y1467-1)*98,0)),"")

So basically, the new person will more often than not just enter the Starting Price (SP), Win dividend, Place dividend and Finish Position into X, Y, Z & AA and the calculations to the right all happen. That part of it is fairly simple, but here's where the issue arises. At times there are horses which do not run; they are withdrawn for whatever reason and need to be deleted.

My question is, how is it possible to allow for the removal of part of a row in a protected sheet....from columns A to AA? If I lock everything after AA and protect the sheet and check Allow User to Delete Rows, the delete option is greyed out when I select cells from A to AA. I need her to be able to delete those cells when required and have the rows below all move up to fill the gap and the calculations to the right still happen correctly. Ideally, I'd prefer not to have it be anything too complex for her, as she is only just about to start and just want to make her job less complicated. Maybe a macro allocated to a button or something, or some VBA which can handle it seamlessly.

I have not yet added a password to each of the 15 sheets in the workbook, but if I do, I can make it the same password for each.

How is this possible?
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,223,880
Messages
6,175,153
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top