Trying to link an excel database/table with yearly calendar. HELP! :)

[JoshuaDane2]

Hi Guys,

I am currently working for an Events Management company and we are looking to create a new overview system for all of the events.

I have created the database table in excel, new events can now be added with the date, location, cost etc...

I am trying to link this with a yearly calendar so that the events will show as a colour/mark, therefore, standing out. I then hope to create another link to a monthly calendar in which more details can be added.

Is there any way of doing this? I have given it a go but it really isnt happening!

Hellpppp, is anybody out theree?


Regards,

Joshua

[Pete_UK]

Post a copy of your file (de-sensitize it if it contains confidential information), then I'll be able to see how your data is laid out and what you want to achieve - put mock-up sheets in there showing how you want the results to appear.

Pete

[JoshuaDane2]

Thank you for getting back to me so quickly!

You would be amazing if you can help.

The link below has an example of how I would like the calendars to work with the data being input. Colours, Year, Month, Day views etc.

Thanks again,

http://chandoo.org/wp/2012/12/26/dow...2013-calendar/

Joshua

[Pete_UK]

If that file on Chandoo's site is similar to what you want, why don't you just download that and play about with it?

Pete

[JoshuaDane2]

I cant transfer the data correctly from that spreadsheet to my own. It is changing all of the dates and I am unable to change the year to make it continous.

I also cant work out how to extract my data from the table to produce colours on the calendar.

I have managed to make a calendar for August with todays date, but the tutorial has no details on how to add in other dates.

Thanks for your time Pete!

Joshua

[Pete_UK]

Well, the file that you attached doesn't make much sense to me, and there are no examples of what you want the result(s) to look like (I'm reluctant to download Chandoo's file, just to try and guess what you want to do).

Your Formula Sheet doesn't have any formulae in it, and half the data seems missing as the first six columns are empty. Your Event Database sheet is empty apart from headings, and Sheet2 and Sheet3 don't seem to be doing anything, so it's a waste of my time looking at it.

I can't work with what you have posted.

Pete

[JoshuaDane2]

Hi Pete,

Give me a few minutes to add some examples in and I will upload again if you dont mind having a quick look again.

I am new to this sorry.

Cheers,

Joshua

[JoshuaDane2]

Hi Pete,

I have had another go at trying to show you what I am trying to achieve.

It still might not be enough for you but if so, I will admit defeat and leave you be!

I thank you for taking the time this afternoon to respond at all.

Cheers,

Joshua

[Pete_UK]

Your examples are based very much on what Chandoo's file produces, and as that already exists I suggest, again, that you use that as a starting point and amend it to suit your own requirements.

Pete

[JoshuaDane2]

Hi Pete,

I have had another go at it today. Managed to get the calendar together on the seperate page to provide the overview.

I am just stuck on how I can:

a)Colour code the event type so that the particular colour will...
b) Fill the Year view calendar in the cells below the date.

I have had a good route this morning and given it my best shot but I'm acctually lost and well out of my depth.

Wondering if you can take a 30s look and point me in the right direction?

Regards,

Joshua

[Pete_UK]

I presume you want to use different colours for the event type (and is this column B of the Event_Data sheet ?). If so, you need to use Conditional formatting on the cells to turn the colour on. So, what colours do you want to use? Do you have any more categories apart from Show, Conference, and Experience BM ?

You can look in Chandoo's file to find how he has set it up in his file (click on a cell with colour in, and then Conditional Formatting | Manage rules). If you set this up for a pair of cells, then you can use the Format Painter to apply the CF to all other cells.

Hope this helps.

Pete

[JoshuaDane2]

Thank you. And once this is complete the dates in the table on the first sheet will show the colour on the calendar?

Cheers,

Joshua

[Pete_UK]

Hi Joshua,

you need to put something in each cell which will trigger the conditional formatting, and you need to have conditional formatting set up in each cell (3 conditions, as you have 3 event types). So, let's choose a cell for which we have an entry in the Event_Data sheet, i.e. 12/09/2013, so we can check on progress as we introduce those features. On the Year_View sheet choose 2013 and select cell F40 and put this formula in the cell:

Please Login or Register  to view this content.

It should display "Show", as that is the event type for that date. You can un-bolden the cell if you wish, but don't worry about seeing the text for now, as when we set up the CF we will use a foreground colour that is the same as the background colour so it will appear to be empty, but coloured. You can copy that formula if you wish to cells AC42 (under 18/12/2013) and V42 (under 21/11/2013) as those are other cells for which we have dates in the Event_data sheet, and so you should see "Conference" and "Experience BM" in those cells respectively, and if you copy it to any other cell it should return a blank (so the formula is doing what we want it to do).

Select F40 again and click on Conditional Formatting | New Rule and click on the bottom choice - Use a formula ... etc. In the box below enter this formula:

=F40="Show"

then click the Format button, choose the Fill tab and choose your colour (e.g. mid-range orange), then click the Font tab, click on the drop-down for Color (sic) and pick the same colour as for the background, then click OK, and OK again and that cell should now show the orange (and you can't see the text).

You now need to repeat that exercise two more times for the other event types, i.e. select F40, click Conditional Formatting | New Rule | Use a formula ..., enter this formula:

=F40="Conference"

Click Format | Fill tab | choose colour (e.g. mid-range purple), click on Font | Color drop-down | choose same colour, then OK and OK. Repeat one more time using the formula:

=F40="Experience BM"

and choose a different colour (e.g. mid-range green) for both foreground and background.

Now, you can copy that cell under all the dates, including all cells that can be dates in different years. By that I mean that if you look at December 2013, the 1st is a Sunday, and so the 30th and 31st can appear as "orphan" days on a row of their own (meaning that a 31-day month can span six weeks using 2 days of the 6th week, and a 30-day month can use the first day of that 6th week, depending which day the first of the month occurs on). So, it's probably best to copy that cell F40 into C6:I6, K6:Q6, S6:Y6 and AA6:AG6, and then you can copy that row into the cells under all the dates on alternate rows.

That should give you the colour display you were seeking, and you can test it out by putting some more event types and dates in the Event_data sheet.

Hope this helps.

Pete

[JoshuaDane2]

Fallen at the first hurdle! The code works in F40 and displays "Show", however, repeats this word in the other date specific cells. Should I just continue through the steps? If I dont get it done today I will mark this as complete and give up haha!

Cheers Pete,

Joshua

[Pete_UK]

Try to work through it again (the complete steps). Ensure you have Calculation options set to Automatic.

I saved a copy of the completed file as I worked through it yesterday, so if needs must I could post that for you - however, it is important for you to work through it yourself if you want to learn these things.

Pete

[Pete_UK]

Joshua,

it just occurred to me that when I said "... You can copy that formula if you wish to cells AC42 (under 18/12/2013) and V42 (under 21/11/2013) ..." you might have copied the formula from the post, in which case all three formulae will be pointing to the same cell (F39) to look for a match. What you need to do, once you have put the formula into cell F40, is select that cell then click on <copy> (or CTRL-C), then move the cursor to cell AC42 and then paste (CTRL-V), then move the cursor to cell V42 and press <Enter>. Then you should see the appropriate event types. Once you have set up the conditional formatting, you should use the same procedure to copy cell F40 into C6:I6, and so on.

Hope this helps.

Pete