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

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

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

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

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

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