Forum home » Delegate support and help forum » Microsoft Excel Training and help » Excel 2003 - conditionally formatting
|
|
|
The UK's most regular Training information: See also: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel 2003 - conditionally formatting
Resolved · Urgent Priority · Version 2003
Reply Tue 14 Sep 2010, 15:29Delegate Emma said...
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
For upcoming training course dates see: Pricing & availability
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
Reply Mon 20 Sep 2010, 10:22Delegate Emma said...
RE: Excel 2003 - conditionally formatting
Dear Nafeesa,
Would it help to resolve this issue if I send you a mock spreadsheet?
Kind Regards,
Emma
Reply Mon 20 Sep 2010, 14:31Delegate Emma said...
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
Tue 28 Sep 2010: Automatically marked as resolved.
|