Mapping Dates to activities onto a calender on another sheet

**Arnoldleg** · 09-21-2009, 05:39 AM

Hello

I'm trying to set up an excel sheet where I have a date next to an activity on one sheet. Then on another sheet I want to see the activity in the relevent month on a calendar.

This is so I can schedule the same activities on different jobs onto a calender and see how they coincide.

Any suggestions welcome please!

Thanks in advance

**DonkeyOte** · 09-21-2009, 05:45 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

**Arnoldleg** · 09-21-2009, 10:21 AM

Apologies DonkeyOte

My descriptions are never very good.
Anyway - please see spreadsheet attached

Many Thanks

Arnold

**DonkeyOte** · 09-21-2009, 11:36 AM

Can you elaborate a little...

On the Schedule sheet you have three different tables each of which contains values for only one row/job be it AAA,BBB,CCC ... so:

-- does only 1 job ever appear in only one of these sub-tables ?
-- what is the logic that determines which job appears in which table ?
-- why is Job sheet AAA different to those of BBB & CCC ?
-- what happens if you have multiple tasks for a given job in a given month (say AAA has three tasks dated in Oct 09)

**Arnoldleg** · 09-21-2009, 11:53 AM

Sorry.....too brief again.

The different tables are for subsequent years, 2008, 2009, 2010.

AAA is one job and has a specific set of tasks which hopefully should not ever be within the same month. They're sequential, but there's a possibilty some may clash. If this were a problem, I could live with them being one month after another.

One job will only ever use one line but could be across 3 years.

Job sheet AAA should be the same as the other, that's my error - sorry.

Thanks again!

**DonkeyOte** · 09-21-2009, 12:26 PM

Re: Years - sorry I should have noticed that.

See if attached helps... there is a tendency to lean towards using INDIRECT given the sheet names are variable and determined by values in Col B however INDIRECT is a Volatile function and given the volume of calcs likely to be performed I would personally opt against it (others are far less anti-Volatile than I am so it's a case of personal preference in part).

I added some common calcs to Job sheets to normalise the dates such that you have a column representing 1st of Month as this simplifies things somewhat in terms of formulae requirements on the calendar itself (ie becomes a simple match [issue of numerous tasks in a specific month being ignored])