Formulae in Excel

The UK's most regular instructor-led training courses.
Training information: advanced+excel+course · Excel training course · Microsoft Excel Training London
See also · excel-courses-london · excel courses in london · excel microsoft training

Resolved · High Priority · Version 2003

2 posts

ReplyWed 24 Jun 2009, 15:54Delegate Mick said...

Mick has attended:
Excel Advanced course

Formulae in Excel

When creating a spreadsheet in Excel and using the EDATE formula. I have to leave all the cells with the default date of 31/01/1900 and enter a new start date in each respective row.
Is there a way of hiding the contents of these cells until a new start date is netered in the next row.

What I am trying to do is create a spread sheet to automatically generate the next date at 3,6,9,and 12 month intervals after entering the new start date. Please can someone offer any advice

Version	Date	Location	Places available	Book	Next place rate (£)
Excel Intermediate 1 day course
Version	Date	Location	Places available	Book	Pay by Card	Pay by Invoice
2007	Thu 18 Mar 2010	Bloomsbury	2		£224	£225
2003	Thu 18 Mar 2010	Limehouse	0	FULL
2007	Mon 22 Mar 2010	Limehouse	3		£224	£225
2003	Tue 23 Mar 2010	Southwark	6		£199	£204
2007	Thu 25 Mar 2010	Rochester (Hoo)	0	FULL
2003	Fri 26 Mar 2010	Limehouse	4		£224	£225
Full Schedule: See all 79 Excel Intermediate course dates. Bookings currently available until 23rd December 2010.

749 posts

ReplyThu 25 Jun 2009, 09:54Trainer Stephen said...

RE: Formulae in Excel

Hi Mick

Thank you for your question

If I follow you correctly, you have a block of cells,each row comprising a date followed by 4 edate functions. Unused rows are set to 31/01/1900 and this date replaced when the row is used.

If this is the case then the simplest solution is to conditionally format the table so that any cells with the value 31/01/1900 have a white font colour. This would then hide them and make the values appear when a new date is typed in.

If i have misunderstood your problem, please do not hesitate to get back to me

Regards

Stephen

· What's mine is yours: Sharing data in Excel
· Reporting and Analysis with Excel
· Formulas In Excel: The Very Basics
· Make More of your Money with Excel 2007
· A Picture Is Worth A Thousand Words (Or Numbers)

Please browse our web site to find out more about
advanced+excel+course and other Microsoft training courses.

Excel tip:

No Zeros

Want all the zeros in your worksheet to appear blank? Choose Tools/Options, Click on the View Tab, Deselect the Zero Values Option, Click OK.
Easy wasn't it? But be aware that these cells are not actually blank, they still contain the value zero. This is important because certain functions (ie AVERAGE) make a distinction between blank cells and those with a zero value.

View all Excel hints and tips