Excel Help

[Logique]

Why is Protect Cells so damned unwieldy!

I protected one cell, now excel will not allow me to vary this to a range of cells.

Tools>Uprotect, Format>Cells, I did all that, but no, excel says it's sticking with my original plan.

I gather you have to protect a cell range, and then protect the whole sheet?

 

[Santob]

Why is Protect Cells so damned unwieldy!

I protected one cell, now excel will not allow me to vary this to a range of cells.

Tools>Uprotect, Format>Cells, I did all that, but no, excel says it's sticking with my original plan.

I gather you have to protect a cell range, and then protect the whole sheet?

No, by default on a new worksheet, all cells are protected.

You just highlight the cells you want to be edittable and go FORMAT>CELLS (or CNTRL F1 for a shortcut) and then unprotect them. So now when you go TOOLS>PROTECT, all the cells are protected except those you'd highlighted.

 

[Logique]

Got it, thanks Santob.

Format>Cells, and then Tools>Protect did the trick, many thanks.

 

[Gringotts Bank]

I have a big spreadsheet that I can't check manually.

Over a period of a year, total shares bought is unequal to total shares sold. How do I find out where CMC has made the error? I have accounted for open trades at the start and end of the period.

I have to do this myself because my accountant won't, and CMC won't help.

 

[Trembling Hand]

You need to show what the spreedie looks like

 

[Gringotts Bank]

stock code, action, trade date, price executed, #shares, consideration
AKP Buy 2016-Aug-01 20 262 10000
AKP Sell 2016-Aug-02 20 262 10000

 

[Trembling Hand]

Hmmm more rather than less makes it easy to help.

I would add a column =IF(B1 = "Buy",B1,B1*-1) Then use a pivot table to group by stock Code and sum. Anything that doesn't add to zero is your answer.

 

[Gringotts Bank]

Hmmm more rather than less makes it easy to help.

I would add a column =IF(B1 = "Buy",B1,B1*-1) Then use a pivot table to group by stock Code and sum. Anything that doesn't add to zero is your answer.

Thanks, I just found it by chance. A 10:1 stock split.
Will keep in mind your method for the future.

 

[Trembling Hand]

Cool actually lucky because there is an error. Should have been

=IF(B1 = "Buy",E1,E1*-1)

E1 being the #shares cell