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
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?
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?