Autofill cels based on two dates in adjacent cels

I'm wanting to use Excel to create a visual representation of what people I have working on what projects and for how long.

Name | Project | Start | End Date | cels I want to fill (1 week per "x")

John | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
John | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
John | Proj C | 2/1/06 | 3/28/06 _|______xxxxxxxx
John | Proj D | 4/1/06 | 5/31/06 _|__________xxxxxxxx
Sally | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
Sally | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
Sally | Proj C | 2/1/06 | 3/28/06 _|____xxxxxxxx
Sally | Proj D | 4/1/06 | 6/1/06 __|__________xxxxxxxx

So I hope the above formats well enough to give a rough illustration. I'd like to input a equations to the cels on the right that result in an "x" in each cel based on whether or not it falls between the start and end dates.

If I change the start or end date I'd like it to auto-fill the x's accordingly, so the result is a visual representation of usage of employees on given projects.

I forsee the headers for the "x" columns to be months, broken down in to weeks, as follows (notice 5 weeks in March):

MONTHS___Jan-06|Feb-06|_Mar-06_|Apr-06| .... and so on...
Weeks____1 2 3 4 1 2 3 4 1 2 3 4 5 1 2 3 4
Cels______x x x x x x x x x x x x x x x x x
Cels______x x x x x x x x x x x x x x x x x
Cels______x x x x x x x x x x x x x x x x x

Each "x" cel needs to know what month and week it is representing and determine whether or not it falls in between the start and end date.

Any ideas?

Assuming...
Name is Col A
Project is Col B
Start is Col C
End Date is Col D

In Col E...
="__"&REPT("_",WEEKNUM(C2)-1)&REPT("x",WEEKNUM(D2)-WEEKNUM(C2))

Format Font to 'Courier'.

Note: The Weeknum() function comes with the Analysis ToolPak which is
installed but not activated when Excel is installed. If "#NAME?" appears
when entering the formula above, the Analysis ToolPak needs to be activated.
Tools>Addins...
Put a checkmark in the Analysis ToolPak box and select OK.

HTH,
--
Gary Brown
gary_brown@ge_NOSPAM.com
If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"William2" wrote:

>
> I'm wanting to use Excel to create a visual representation of what
> people I have working on what projects and for how long.
>
> Name | Project | Start | End Date | cels I want to fill (1
> week per "x")
>
> John | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
> John | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
> John | Proj C | 2/1/06 | 3/28/06 _|______xxxxxxxx
> John | Proj D | 4/1/06 | 5/31/06 _|__________xxxxxxxx
> Sally | Proj A | 1/1/06 | 2/28/06 _|__xxxxxxxx
> Sally | Proj B | 1/15/06 | 2/28/06 |____xxxxxx
> Sally | Proj C | 2/1/06 | 3/28/06 _|____xxxxxxxx
> Sally | Proj D | 4/1/06 | 6/1/06 __|__________xxxxxxxx
>
> So I hope the above formats well enough to give a rough illustration.
> I'd like to input a equations to the cels on the right that result in
> an "x" in each cel based on whether or not it falls between the start
> and end dates.
>
> If I change the start or end date I'd like it to auto-fill the x's
> accordingly, so the result is a visual representation of usage of
> employees on given projects.
>
> I forsee the headers for the "x" columns to be months, broken down in
> to weeks, as follows (notice 5 weeks in March):
>
> MONTHS___Jan-06|Feb-06|_Mar-06_|Apr-06| .... and so on...
> Weeks____1 2 3 4 1 2 3 4 1 2 3 4 5 1 2 3 4
> Cels______x x x x x x x x x x x x x x x x x
> Cels______x x x x x x x x x x x x x x x x x
> Cels______x x x x x x x x x x x x x x x x x
>
> Each "x" cel needs to know what month and week it is representing and
> determine whether or not it falls in between the start and end date.
>
> Any ideas?
>
>
> --
> William2
> ------------------------------------------------------------------------
> William2's Profile: http://www.excelforum.com/member.php...o&userid=31023
> View this thread: http://www.excelforum.com/showthread...hreadid=506927
>
>

Gary,

Thank you for taking the time to help me with this function. I gave your idea a whirl, and while it’s very much like what I described, it’s not quite doing the trick, just in terms of formatting. I wonder if I might send you a mockup excel file of what I’m working on that shows the columns set up and the grid?

If you can imagine a grid just to the right of the start and end date, and each column in the grid represents a particular week. Each cel in the column, as I imagined it would work, should know what week in what month it represents (from the data in the header, which currently is just text, which I think is part of the problem).

So I'm seeing the empty cels to the right of the end date as individual functions in each cel that somehow ref the week # from above.

Where you see the equation you gave me is working, and just is set in to the very first field under Jan-06 w1, it’s just floating x’s out to the right. I understand your idea about using Courier for monospacing, but I’m not able to have excel match the columns at all. I’ve tried a number of different ways, no luck. So what I’m thinking is that each w1, w2 etc., should be a date/week# field, instead of text as it is now. And for each cel in the grid below to somehow know what week # it represents and mark an “x” if that week falls between the start and end date.

If this makes it clearer, great! If not, let me know and maybe it would help to email you the file. I really appreciate whatever help you can give. And if it doesn’t work, I’ll look for an alternative.

Thanks again!

Rick

One thing I just realized in looking over my original post. I had included "_____" not because I wanted the underline/spacing to appear in my spreadsheet, but because I wanted the thread to display the formatting correctly to show the visual of what I'm looking for.