Hi,

Assume that the order of the names on both sheets is the same (assumed from
your illustration below). Also, enter 12/2009 and 12/1/2009 I.e. 1 December
2009 in cell F2 of the Rollup sheet

You may use this to determine the BeginDate. Copy down till where required

=if(Data!$B2<F2,F2,Data!$B2)

To determine the end date, use the following:

=if(and(Data!$B2F2,Data!$B2<eomonth(F2,0)),Data!$ B2,eomonth(F2,0))

Since the order of names remains the same, the FSource can simply be copied
and pasted

I am not clear about the days count - please clarify

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dax Arroway" wrote in message
...
So if the Data sheet is:

Name StartDate EndDate FSource
John Smith 11/25/09 12/20/09 Blue
Fred Jones 11/26/09 01/15/10 Green
Bill Blast 12/15/09 01/10/10 Blue

And I selected a range of 12/2009 on the RollUp sheet.
The result on the RollUp sheet would be:

Name BeginDate EndDate FSource DaysCount
John Smith 12/01/09 12/20/09 Blue 19
Fred Jones 12/01/09 12/31/09 Green 31
Bill Blast 12/15/09 12/31/09 Blue 16

Please let me know if there's other questions.
--Dax
--
I would give my left hand to be ambidextrous!


"Ashish Mathur" wrote:

Hi,

So what do you need help with - determining start date or end date

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Dax Arroway" wrote in message
...
I'm hoping someone can help. I'm working on a worksheet (Rollup!) that
gives
me a count of days that a person is present in a given month from
information
pulled from another sheet (Data!).

Data! includes Name (Column C), StartDate (A), EndDate (N), and FSource
(L).

Rollup! needs to include columns: Name, BeginDate, EndDate, FSource,
and
DaysCount. But it's funky.

For starters, there will be two dropdowns on Data!, one for year (A1),
one
for month (B1) used to filter the records pulled from Data! for the
specified
year and month.

For BeginDate I need it to be either the first day of the month or the
StartDate depending on when the person started. For example if my
focus
month is December, 2009, and one person's BeginDate is 11/20/09, then
the
BeginDate next to their name needs to be 12/01/09. I'm trying to count
the
days the person is present in only that month.

For EndDate, same thing. If the person ended within the month
(EndDate=12/24/09), then I need that date, otherwise, if the person is
still
there through the last day of the month, I need the last day of the
month
inserted. So if this person was still there at the end of December,
EndDate
would be 12/31/09. Also, if there is no EndDate on Data!, then the
last
day
of the selected month should be inserted.

For DaysCount, I need it to simply count the days between BeginDate and
EndDate with one exception. If the EndDate occurs within the month of
focus
(ie EndDate= 12/15/09 for December), DaysCount needs to be -1 day
(because
the last day doesn't count).

For FSource I simply need to pull over the cooresponding FSource from
Data!.

Can anyone help with a formula for this?

Thanks SO MUCH in advance. I know it's complicated but I'm sure it's
possible. I unfortunately lack the Excel coding skill to get it done!
--Dax
--
I would give my left hand to be ambidextrous!