Emma has attended:
Excel Advanced course
Excel 2003 - conditionally formatting
Basically I have a spreadsheet which shows people's reasonable adjustment needs ie travel etc that needs to perform two functions:
1. Firstly I need to write a formula with two specific date ranges so when you enter the date of request in the next cell it will automatically sort when you should review this (preferably with colour formatting as well.)
I need to conditionally format one column so it populates the column next to it.
The two period ranges are:
Jan - up to July = review end March
July - up end Jan = review are end Sept
These formulas will need a true value. If you could give me any hints or tips that would be much appreciated.
2. Secondly is there a way I can hide a column so only a certain person with access can access this confidential information? Or should I link a different spreadsheet?
Thanks,
Emma
RE: 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
RE: Excel 2003 - conditionally formatting
Dear Nafeesa,
Would it help to resolve this issue if I send you a mock spreadsheet?
Kind Regards,
Emma
RE: Excel 2003 - conditionally formatting
Dear Nafeesa,
Should I used a named range for my formula, as I need to show give a range rather than use the less than option in the formula is category of Conditional Formatting. Can it also populate the cell next to it to give the review date?
Jan - June = July review Oct-01 Mar-31 Jul-01
July - Dec = Jan review Apr-01 Sep-30 Jan-01
Kind Regards,
Emma
RE: Excel 2003 - conditionally formatting
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
