Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I'm hoping someone can help me write a function, please, please, please.
What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I should have added that ($surveys_due) should look like this:
A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
Yes that's right, and that function works but it displays Survey Past Due in
blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I'm going to assume that your data in the $surveys sheet is in the form of:
A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I forgot to add a comment in the code to say that if the sheet name is not
$surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
Thanks for the reply. Close, but not quite. This is what I have:
Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I am sure that it is possible to do it automatically with formulas but I ran
into problems with tied dates and it is too late tonight tolook at it. In the mean time here is some code that will do what you want but if anyone can do it with formulas the feel free to jump in. Option Explicit Sub Surveys_Due() Dim WS As Worksheet Dim sName As String Dim sMsg As String Dim i As Integer Dim r As Integer If ActiveSheet.Name < "Surveys Due" Then Exit Sub Set WS = Sheets("Surveys") Range("A:A").ClearContents Cells(1, 1).Value = "Survey Due Report" With WS r = 2 For i = 2 To 5 If Application.Count(.Cells(2, i), _ .Cells(4, i)) 0 Then If Date - Application.Max(.Cells(2, i), _ .Cells(4, i)) = 70 Then sName = .Cells(1, i).Value sMsg = "Survey Due" End If If Date - Application.Max(.Cells(2, i), _ .Cells(4, i)) = 90 Then sMsg = "Survey Past Due" End If If sName < "" Then Cells(r, 1).Value = sName r = r + 1 Cells(r, 1).Value = sMsg r = r + 1 End If End If sName = "" sMsg = "" Next i End With End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Thanks for the reply. Close, but not quite. This is what I have: Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
I think you have your rows and columns mixed up. Does your data look
like this: Name1 date1 date2 date3 Name2 date1 date2 date3 Name3 date1 date2 date3 Name4 date1 date2 date3 Name5 date1 date2 date3 etc., with maybe some dates missing? If that is the case then your names are in column A, date1 in column B etc. I think Sandy assumed from your earlier description that all the data was in one column. Pete On Oct 5, 10:12 pm, Dax Arroway wrote: Thanks for the reply. Close, but not quite. This is what I have: Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax- Hide quoted text - - Show quoted text - |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
Hi Dax,
I see where Pete is coming from with your saying: There are only 4 columns and every row is a new record/person. But assuming that you meant "There *are* only 4 columns and every *Column* is a new record/person." so that your data looks like: Name_____Frank____Betty____David_____Debbie Survey1___5/29/07___________9/25/07___3/15/07 Survey2___6/30/07____________________5/15/07 Survey3_____________________________7/30/07 It is possible with a helper row,(in fact I'm still convinced that it is possible with formulas only). In the Surveys sheet cell B5 enter the formula: =IF(COUNT(B2:B4)=0,0,IF(TODAY()-MAX(B2:B4)69,TODAY()-MAX(B2:B4)+1-COLUMN()/100,0)) and copy across to E5 on the fill handle. Row 5 may now be hidden if required. In the Surveys Due sheet cell A2 enter the formula: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",INDEX(Surveys!$B$ 1:$E$1,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((ROW( )-1)/2,1)),Surveys!$B$5:$E$5,0))) and in Surveys Due A3 enter: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",IF(INDEX(Surveys! $B$5:$E$5,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((R OW()-1)/2,1)),Surveys!$B$5:$E$5,0))90,"Survey Past Due","Survey Due")) Highlight A2:A3 and drag both down on the fill handle to A9. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Thanks for the reply. Close, but not quite. This is what I have: Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days does need to pass between them no matter how long it's been between the previous 2 surveys.) Can anyone please help me with this? Thanks for your help in advance. - Dax |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
OH SO CLOSE! Thank you for all the help! (And the formulas too! Good
Golly!) But Pete_UK had it right. The A column is Name, B column is first date, C column is second date, and D is third date. So: Name1 date1 date2 date3 Name2 date1 date2 date3 Name3 date1 date2 date3 Name4 date1 date2 date3 Name5 date1 date2 date3 is correct, with the names running on down the list (so far there are 278 records/rows) with some dates missing. What I'm looking for is a list of all the people who are due to have a survey done, but IF a person has not done a survey (no dates entered), the names should not be included in the report. So if I have: Frank 01/01/07 <blank <blank Betty <blank <blank <blank Sue 05/04/07 07/05/07 09/10/07 George <blank <blank <blank ....and so on and so forth going down the list through 278 and-then-some names, On the Surveys_Due sheet, I want: Frank Past Due Sue Due (supposing the latest date is between 70-90 days) (and George, Betty, and all others without dates would NOT be on the list). I'm so sorry about all the confusion! It's basically a report of people that are due and past due for surveys. This would be SO MUCH easier if they actually did the database in Access but "they" love pivot table so much that they had us do this database in Excel! So now I have to come up with this way of getting a report out of it. I agree with Sandy, however; I think this could entirely be done with functions and that's what I'm after. I'm hoping this is easy and thanks again for all the support! - Dax "Sandy Mann" wrote: Hi Dax, I see where Pete is coming from with your saying: There are only 4 columns and every row is a new record/person. But assuming that you meant "There *are* only 4 columns and every *Column* is a new record/person." so that your data looks like: Name_____Frank____Betty____David_____Debbie Survey1___5/29/07___________9/25/07___3/15/07 Survey2___6/30/07____________________5/15/07 Survey3_____________________________7/30/07 It is possible with a helper row,(in fact I'm still convinced that it is possible with formulas only). In the Surveys sheet cell B5 enter the formula: =IF(COUNT(B2:B4)=0,0,IF(TODAY()-MAX(B2:B4)69,TODAY()-MAX(B2:B4)+1-COLUMN()/100,0)) and copy across to E5 on the fill handle. Row 5 may now be hidden if required. In the Surveys Due sheet cell A2 enter the formula: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",INDEX(Surveys!$B$ 1:$E$1,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((ROW( )-1)/2,1)),Surveys!$B$5:$E$5,0))) and in Surveys Due A3 enter: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",IF(INDEX(Surveys! $B$5:$E$5,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((R OW()-1)/2,1)),Surveys!$B$5:$E$5,0))90,"Survey Past Due","Survey Due")) Highlight A2:A3 and drag both down on the fill handle to A9. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Thanks for the reply. Close, but not quite. This is what I have: Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help w/complex Due Date Function
Hi
If you added 2 more columns to your data, headed Lastdate and Survey Due E2 = MAX(B2:D2) This would return 00/01/1900 for entries where there were no dates at all in columns B:D) F2 =IF(E2<1/1/1900,"",IF(TODAY()-E290,"Past Due",IF(TODAY()-E270,Due for Survey,""))) You could just apply an Autofilter to row 1, and use the dropdown on Survey due to Select Non Blanks if you wanted a list of both Due and past Due, or select one of the types to just shown those people on their own. You could create a Pivot Table for their data if required, (but I hardly think it necessary). If you did want to, then mark your dataDataPivot TablesFinish Drag Name to Row area Drag Survey due to Column Area Drag Survey Due again to the Data area, as Count of Survey Due From the Column dropdown, de-select the blank (empty category) -- Regards Roger Govier "Dax Arroway" wrote in message ... OH SO CLOSE! Thank you for all the help! (And the formulas too! Good Golly!) But Pete_UK had it right. The A column is Name, B column is first date, C column is second date, and D is third date. So: Name1 date1 date2 date3 Name2 date1 date2 date3 Name3 date1 date2 date3 Name4 date1 date2 date3 Name5 date1 date2 date3 is correct, with the names running on down the list (so far there are 278 records/rows) with some dates missing. What I'm looking for is a list of all the people who are due to have a survey done, but IF a person has not done a survey (no dates entered), the names should not be included in the report. So if I have: Frank 01/01/07 <blank <blank Betty <blank <blank <blank Sue 05/04/07 07/05/07 09/10/07 George <blank <blank <blank ...and so on and so forth going down the list through 278 and-then-some names, On the Surveys_Due sheet, I want: Frank Past Due Sue Due (supposing the latest date is between 70-90 days) (and George, Betty, and all others without dates would NOT be on the list). I'm so sorry about all the confusion! It's basically a report of people that are due and past due for surveys. This would be SO MUCH easier if they actually did the database in Access but "they" love pivot table so much that they had us do this database in Excel! So now I have to come up with this way of getting a report out of it. I agree with Sandy, however; I think this could entirely be done with functions and that's what I'm after. I'm hoping this is easy and thanks again for all the support! - Dax "Sandy Mann" wrote: Hi Dax, I see where Pete is coming from with your saying: There are only 4 columns and every row is a new record/person. But assuming that you meant "There *are* only 4 columns and every *Column* is a new record/person." so that your data looks like: Name_____Frank____Betty____David_____Debbie Survey1___5/29/07___________9/25/07___3/15/07 Survey2___6/30/07____________________5/15/07 Survey3_____________________________7/30/07 It is possible with a helper row,(in fact I'm still convinced that it is possible with formulas only). In the Surveys sheet cell B5 enter the formula: =IF(COUNT(B2:B4)=0,0,IF(TODAY()-MAX(B2:B4)69,TODAY()-MAX(B2:B4)+1-COLUMN()/100,0)) and copy across to E5 on the fill handle. Row 5 may now be hidden if required. In the Surveys Due sheet cell A2 enter the formula: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",INDEX(Surveys!$B$ 1:$E$1,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((ROW( )-1)/2,1)),Surveys!$B$5:$E$5,0))) and in Surveys Due A3 enter: =IF(INDEX(Surveys!$B$5:$E$5,MATCH(LARGE(Surveys!$B $5:$E$5,CEILING((ROW()-1)/2,1)),Surveys!$B$5:$E$5,0))=0,"",IF(INDEX(Surveys! $B$5:$E$5,MATCH(LARGE(Surveys!$B$5:$E$5,CEILING((R OW()-1)/2,1)),Surveys!$B$5:$E$5,0))90,"Survey Past Due","Survey Due")) Highlight A2:A3 and drag both down on the fill handle to A9. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Thanks for the reply. Close, but not quite. This is what I have: Sheet="Surveys" Row A=Header Row A1=Name A2=Survey1 A3=Survey2 A4=Survey3 B1=Frank B2=5/29/07 B3=6/30/07 B4=<blank C1=Betty C2=<blank C3=<blank C4=<blank D1=David D2=9/25/07 D3=<blank D4=<blank E1=Debbie E2=3/15/07 E3=5/15/07 E4=7/30/07 There are only 4 columns and every row is a new record/person. What I want is: Sheet="Surveys_Due" A1="Surveys Due Report" (Header) B1=Frank B2=Past Due C1=Debbie C2=Due (supposing the last date is between 70 and 90 days ago) I hope that makes it clearer. And thanks so much for your help! - Dax "Sandy Mann" wrote: I forgot to add a comment in the code to say that if the sheet name is not $surveys due the change the name in the code to the real sheet name. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... I'm going to assume that your data in the $surveys sheet is in the form of: A1:Name1 (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) A5:<Blank Row A6:Name2 (type:text) A7:Survey1 (type:date) A8:Survey2 (type:date) A9:<No Date etc. and you want A1:Name1 (type:text) A2:Survey3 (type:date) A3:<Blank Row A4:Name2 (type:text) A5:Survey2 (type:date) etc. There may be more sophistocated way of doing this but try: on the $surveys Due sheet: A1: =IF('$surveys'!A1="","",'$surveys'!A1) A2: =IF(MAX('$surveys'!A2:A4)=0,"",IF(TODAY()MAX('$su rveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due",""))) A3: x A4: y A5: z Yes A3:A5 have the letters x,y, & z Highlight A1:A5 and copy down as far as you have data, (or could possibly have in the future), in the $surveys sheet. Finally run this macro: Sub DeleteThem() Dim rLast As Long Dim x As Long If ActiveSheet.Name < "$surveys due" Then _ Exit Sub Application.ScreenUpdating = False rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "x" Or _ Cells(x, 1).Value = "y" Then Cells(x, 1).EntireRow.Delete End If Next x rLast = Cells(Rows.Count, 1).End(xlUp).Row For x = rLast To 1 Step -1 If Cells(x, 1).Value = "z" Then Cells(x, 1).ClearContents End If Next x Application.ScreenUpdating = True End Sub -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... Yes that's right, and that function works but it displays Survey Past Due in blank rows. I need to ignore those rows. I also need the A1:Name that corrolate with the returned functions. And lastly I need all the blank rows (the ones with no data in them, the ones that are ignored) removed so I don't have a list with a bunch of empty rows in it. Just a little more help please? - D. "Sandy Mann" wrote: It sounds like you only want the formula in A2 of the $surveys_due sheet. If so try: =IF(TODAY()MAX('$surveys'!A2:A4)+90,"Survey Past Due",IF(TODAY()MAX('$surveys'!A2:A4)+70,"Survey Due","")) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Dax Arroway" wrote in message ... I should have added that ($surveys_due) should look like this: A1:<name from $surveys:A1 A2:<returned Value from function "Dax Arroway" wrote: I'm hoping someone can help me write a function, please, please, please. What I have: I have a spreadsheet ($surveys) with one record per line. The columns a A1:Name (type:text) A2:Survey1 (type:date) A3:Survey2 (type:date) A4:Survey3 (type:date) What I need: I need a function that tells me when a survey is due and when it's past due for each person. Names and dates will be entered by hand. The function needs to function like this: If there is no date in Survey1, disregard that row. If there is a date in Survey1, but not it Survey2 or 3, use Survey1's date. If there is a date in Survey2, but not Survey3, use Survey2's date. And if there is a date in Survey3, use that date. (In other words, use the latest date.) The date needs to be compared to Today's Date and the words "Survey Due" or "Survey Past Due" needs to be returned. "Survey Due" needs to be returned if after 70 days and "Suvey Past Due" if after 90 days. I then need this list to be truncated (all the blank cells removed) and displayed with corrolating names on another speadsheet ($surveys_due). What I'm after: Surveys need to be completed every 90 days for each person. What this function should do is give me a list of people who are due or past due to take the survey no matter if it's the first, second, or third survey they've taken. It's a "tickler" to let me know when to get folks in to take their next survey and when they're past due. I'm not interested in those who haven't done a survey. (I realize that this does not give me consecutive dates for each next survey, every 90 days down the line. The "next" survey should come after 90 days of the previous one; in other words I don't need them to be every 90 days consecutively from the first one, 90 days |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex Date Functions | Excel Worksheet Functions | |||
Complex Function? | Excel Worksheet Functions | |||
Complex Date Formula | Excel Worksheet Functions | |||
complex function | Excel Worksheet Functions | |||
complex date function | Excel Worksheet Functions |