Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Match dates to select data (2000/SP3)

    I am looking for some assistance on selecting data based on a match between two dates.
    A master worksheet has mmm-yy column headings and a row for each project being worked. Each project has a separate worksheet which lists, among other things, a column of milestone due dates (dd/mm/yy format) and a column of my expected income due on those dates.

    I want the master worksheet to show the expected income for the project when the mmm-yy column heading matches the milestone date. I have tried all sorts of date functions and lookups but can't get it working. Any clues please?? A sample worksheet is attached.

    Alex

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Match dates to select data (2000/SP3)

    Select T2:CB2 on the REVENUE RECORD AND FORECAST sheet. Make sure that T2 is the active cell within the selection. Enter this formula:

    =SUMPRODUCT((DATE(YEAR('Project A'!$D$3:$D$8),MONTH('Project A'!$D$3:$D$8),1)=T$1)*'Project A'!$F$3:$F$8)

    Confirm with Ctrl+Enter. (You can also select T2, enter the formula, then fill to the right.) The formula takes D38 on the Project A worksheet, calculates the first day of the month and compares that to T1. This results in a series of TRUE/FALSE values. Since TRUE = 1 and FALSE = 0, multiplying withF3:F8 and summing results in adding only the values with a matching date.

  3. #3
    New Lounger
    Join Date
    Sep 2002
    Location
    Sydney, New South Wales, Australia
    Posts
    24
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Match dates to select data (2000/SP3)

    Great work, thanks Hans. That gives me an introduction to a function (sumproduct) I have never used before.

    Regards
    Alex

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •