Fairly Complicated Calendar question

[Jayhawks88]

I'm attempting to create a scheduling calendar in Excel 2007 (but it needs to
be compatible with 2003) and I could use some help.

It's going to be used for work purposes and what I'm trying to setup is one
main calendar and then one secondary calendar for each project in my group. I
really don't care if they all reside on one sheet or multiple sheets.

The main calendar is primarily going to be used as a quick overview of what
is going on in the entire group. The calendar will consist of dates across
the top row and names down the first column. The basic calendar setup will be
the same for each of them.

The ultimate goal is for each manager to be able to schedule people on their
own project's calendar and then have Excel reflect these inputs on the main
calendar. In other words, if a manager (using their calendar for project A)
schedules employee 1 to be working on June 1, it will show employee 1 working
on June 1 on the main calendar.

That part isn't that bad, and I've been able to get some rudimentary working
version of it up and sort of functional. The part that takes it beyond my
capabilities is that I also need it to designate that employee as unavailable
for those dates on each of the other project calendars.

For example, using the previous example, employee 1 would be shown (either
via an X, conditional formatting to make the cell red or some other
designation) as being unavailable on each of the other project calendars.

I've tried all the ways I can think of to make this work, but I always end
up with circular formula errors.

If anyone has any suggestions I would appreciate them. Thanks for the help.

 

[Shane Devenshire]

Hi Jay,

The conditional formatting issue: You can do it but its fairly complicated.
First, if the other calendars are on separate sheets you must use range
names for each range.

Here is the conditional formatting formula I created:

=OR(INDEX(Table1,ROW(A1),COLUMN(A1))="x",INDEX(Table2,ROW(A1),COLUMN(A1))="x")

The ranges Table1 and Table2 are on other sheets than the one I will be
formatting. The table ranges only include the portions of the tables where
the x's are.
Each sheet will have a slightly different formula because it will include
the "other" sheets, never the current sheet.

Cheers,
Shane Devenshire
Microsoft Excel MVP