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?


--
William2
------------------------------------------------------------------------
William2's Profile: http://www.excelforum.com/member.php...o&userid=31023
View this thread: http://www.excelforum.com/showthread...hreadid=506927

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.
ToolsAddins...
Put a checkmark in the Analysis ToolPak box and select OK.

HTH,
--
Gary Brown

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


--
William2
------------------------------------------------------------------------
William2's Profile: http://www.excelforum.com/member.php...o&userid=31023
View this thread: http://www.excelforum.com/showthread...hreadid=506927

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.


--
William2
------------------------------------------------------------------------
William2's Profile: http://www.excelforum.com/member.php...o&userid=31023
View this thread: http://www.excelforum.com/showthread...hreadid=506927