Show a certain cell depending on data from a drop down menu.

Hi,

The layout of your calendar does not easily lend itself to analysis, and the fact that you're using font colours and needing to interpret those with a cell background colour, almost certainly means that you'll need some Visual Basic (Macro) code to stand any chance of achieving this.

Before going down the VBA route though, it strikes me that there must be some simple algorithm that could work out the publication date from first principles. For instance at first glance it seems that a book received up to and including a Wednesday will publish on the Friday of that week, otherwise it will publish on the Friday of the following week.

If you can confirm this, or offer a rule of your own (i.e. how did you put the calendar together - what was the logic you applied), then no doubt we can give you a simple formula that will avoid the need for calendars at all.

Rgds

Hi Richard,

Wow that was fast!

The way i work out the publication date is that if a book comes into the warehouse before 12.00PM on Wednesday it will publish on the friday.

It if comes into the warehouse after 12.00 pm on a wednesday it will not publish until the following friday.

The problem I have is that books do not publish on fridays that are bank holidays, they will publish the friday after, these are marked with a BH on the calendar.

Thank you very much for your help!

Hi,

See if the attached does what you want. It doesn't use the calendar sheets, just standard Excel Date arithmetic.

If it wasn't for the double consecutive Friday bank holiday on 25/12/09 and 1/1/10 then the formula could be simplified further. You can see the small table of Friday bank holidays in F2:G5 on sheet 2. Just add as necessary, the range called 'holidays' extends down to row 20 at the moment so there's room for several more.

Just enter your date in A6 and pick either AM or PM in B6 and D6 will show the publishing date.

HTH

Thank you, I am back at work on monday so won't be able to try it until then but I will let you know how it works out.

Thank you for your help

Richard,

Thank you so much for this, it is exactally what I needed!

I don't know how you worked that out, but thank you!

Hi Again,

Sorry to be a pain, i've just noticed that the last publishing date that it will show is 9th of April 2010.

Our year runs until the end of april. Is there any way of showing publication dates up to and including the 30th of April 2010?


Thank you for your help,

Nick

Hi Nick,

That's an odd one. It seems to be limiting itself to the last holiday period. I'll take a look.

Rgds

Hi Nick,
Substitute this formula in D6

Please Login or Register  to view this content.

Rgds

Thank you very much Richard, you have been a big help!

OK Nick, thanks for the feedback and glad to help.

Please go back to the original and mark the thread as SOLVED and if appropriate rate the answers (the small balance scales icon).

Regards

Hi,

Try the following correction for the Wednesday AM/PM problem

Please Login or Register  to view this content.

Rgds

Hi Richard,

Thanks it has worked for the first half of the week but for thursday and friday it has not, these should publish on the next friday.

I.E. a book that comes in on 08/01/09 should publish on 16/01/09

Hi,

Sorry about that one. Try substituting this formula.

Rgds

Please Login or Register  to view this content.