Excel 2003 - conditionally formatting

Hi Emma,


Thank you for your question.


Apologies in the delay in getting back to you. Your question has been referred to an Excel trainer.

We will endeavour to get a response to your question by Monday.


Kind Regards,


Nafeesa

Microsoft Office Specialist Trainer

Hi Emma, sorry for the delay! As luck would have it I'm around and can answer your query today:

1) This is a difficult thing to achieve. You can't use conditional formatting to alter actual values in cells, only their formatting properties. Remember you use formulas in other cells to cite and manipulate the values in others. However, what you are trying to do has a precedent. Occasionally we get people asking how they can enter their football team scores into a spreadsheet and for the league rankings to automatically update and sort accordingly. I reckon you need to do something similar. As a task, there are more stages than I can feasibly post on this forum. But here's a handy link:

http://www.auditexcel.co.za/Automatic %20Sorting%20Project.html#eighth

Have a look through it and see if you can apply it to what you are doing. What you are trying to achieve is possible, very effective when in place, but takes some tinkering!

2) The simplest way to do this is to hide the column and then password protect the worksheet (via the Tools dropdown). Do this and you can't unhinde the column unless you know the password.

Hope this helps,

Anthony