Match dates and names on different sheets and colour cells

[ExCelNoob_]

Hi there!

First time post here.

I am trying to find a solution to my problem.
Currently I have Sheet 1 with names running down Column A and Dates running over Row 1, Much like a calendar type thing (date running consecutively across the rows)
On the Sheet 2 I have Name on Column A, Date Start on Column B and Date Finish on Column C.

I am trying to come up with a code that, when I add the dates (Start and Finish) next to the names in Column A on Sheet 2, it colours in the cells in Sheet 1 with the dates from Sheet 2.

The intent is so that when the dates for Sheet 2 are entered next to the Name John (E.g. 1/1/10 - 3/1/10), Sheet 1 has the 1/1/10, 2/1/10 and 3/1/10 colored and 'blocked' off so to speak, next to John's name.

Please find the attached images as references to the problem...

Any help that anyone could give would be greatly appreciated

Cheers!

[teylyn]

Hello ExcelNoob.

if you want help with Excel, please post Excel files, not images.

cheers,

[Richard Buttrey]

Hi,

Welcome to the forum. Please take a moment to read the rules and suggestions. Had you done so you would have known we prefer you to upload example workbooks not just pictures. None of us are inclined to start creating your workbook from scratch just to test any solutions we offer.

You will need to use the formula options within the conditional formatting functionality. The formulae will include MATCH() to detect the relevant row for each person and IF() tests to determine whether the dates on row 1 are between the To/From dates for each person with the formatting being set to the colour you want to show.

Rgds

[ExCelNoob_]

Hey Guys,

Cheers for the reply

Workbook is attached

Thanks again!

[teylyn]

Hello,

In cell B3 enter this formula

Please Login or Register  to view this content.

Format the cell to be white text on white background or with custom format ;;;

Then apply a conditional format to the cell, using cell equals True or using a formula

=B3=TRUE

Set the background/fill color.

Then copy that cell down and across.

cheers

[ExCelNoob_]

That works great, the only thing is I would like to be able to add notes and maybe type stuff in the cells, is there a way to do it so the formula isnt in the cells?
maybe through VBA or something?

Many thanks on that formula it works a treat just as you said.

Cheers

[teylyn]

With the formula in the cells, you cannot add any other content. But you could create comments for cells, which can be set to display permanently or pop up when the mouse pointer hovers over the cell.

Another possibility would be to duplicate the Gantt chart. One version has the formulas in the cells, the other one does not. Both are on the same sheet. You can then create conditional formatting in the empty Gantt, referencing the respective cells in the one with the formulas. This provides you with the possibility of adding comments. The Gantt with the formulas can be hidden.

See attached for an example of this.

cheers

[ExCelNoob_]

Hi,

Thanks again.

Im still having problems getting my head around it tho.
The problem is that having the small amount of names there, the referencing method works great, but it doesn't allow for expansion, without editing.
If I have hundreds of names going in and out of the calendar and hundreds of entries adding to the dates, then it would be more labour intensive than manually selecting the cells and colouring them each.

Any ideas?

[teylyn]

Well, maybe it's a case of wrong tool for the job. Use a proper project management tool that is designed to handle Gantt charts. Excel is not one, and trying to make it behave like one will involve work.

www.chandoo.org has some great tutorials on using Excel for project management. Maybe you'll find some inspiration there.