Finding a duplicate entry with no end date
 

Hi all
Hoping you can help out? I have a list of names with start and end date (of
placement). When I enter a new entry I want to be able to identify if a
previous entry does not have an end date and then a warning pops up for the
user saying something like "another placement still current".
Thanks in anticipation.

Finding a duplicate entry with no end date
 

Hi,

You don't tell us your data layout so this assumes

Col A Col B Col C
Name Start End

Put this in a cell
=IF(VLOOKUP(INDEX(A:A,MATCH(REPT("z",10),A:A,1),1) ,A1:C6,3,FALSE)=0,"Another
placement still current","")

The formula has wrapped as I pasted it but it all goes in a single cell.
Every time a new name is added to column A it will look for another instance
of that name and check if column C is blank and if it is it will display your
warning. Ir always checks the last entry in column A so there is no need to
alter the formula when a new name is added.

Mike

"Janelle S" wrote:

Hi all
Hoping you can help out? I have a list of names with start and end date (of
placement). When I enter a new entry I want to be able to identify if a
previous entry does not have an end date and then a warning pops up for the
user saying something like "another placement still current".
Thanks in anticipation.

Finding a duplicate entry with no end date
 

There was an error in the last formula

=IF(VLOOKUP(INDEX(A:A,MATCH(REPT("z",10),A:A,1),1) ,INDIRECT("A1:C" &
SUMPRODUCT(MAX((ROW(A1:C1000))*(C1:C1000<"")))),3 ,FALSE)=0,"Another
placement still current","")

"Janelle S" wrote:

Hi all
Hoping you can help out? I have a list of names with start and end date (of
placement). When I enter a new entry I want to be able to identify if a
previous entry does not have an end date and then a warning pops up for the
user saying something like "another placement still current".
Thanks in anticipation.

Finding a duplicate entry with no end date
 

Assume names, start & end dates will be entered in cols A to C, from row2 down

Select col A, click Data Validation
In Settings tab,
Allow: Custom
Formula:
=NOT(AND(A1<"",COUNTIF(A$1:A1,A1)1,INDEX(C$1:C1, MATCH(A1,A$1:A1,0))=0))
Click on the Error Alert tab
(Answer "yes" to dismiss the prompt that formula currently evaluates to an
error)
Input the error message, eg: Stop, another placement still current!
Click OK

Test it out ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Janelle S" wrote:
.. I have a list of names with start and end date (of placement).
When I enter a new entry I want to be able to identify if a
previous entry does not have an end date and then a warning
pops up for the user saying something like
"another placement still current".

Finding a duplicate entry with no end date
 

Thanks Max. One small problem - the error alert pops up if there is an end
date - I want it to pop up if there is no end date. Any suggestions??

"Max" wrote:

Assume names, start & end dates will be entered in cols A to C, from row2 down

Select col A, click Data Validation
In Settings tab,
Allow: Custom
Formula:
=NOT(AND(A1<"",COUNTIF(A$1:A1,A1)1,INDEX(C$1:C1, MATCH(A1,A$1:A1,0))=0))
Click on the Error Alert tab
(Answer "yes" to dismiss the prompt that formula currently evaluates to an
error)
Input the error message, eg: Stop, another placement still current!
Click OK

Test it out ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Janelle S" wrote:
.. I have a list of names with start and end date (of placement).
When I enter a new entry I want to be able to identify if a
previous entry does not have an end date and then a warning
pops up for the user saying something like
"another placement still current".

Finding a duplicate entry with no end date
 

"Janelle S" wrote:
Thanks Max. One small problem - the error alert pops up if there is an end
date - I want it to pop up if there is no end date. Any suggestions??

That shouldn't be. Perhaps you implemented it incorrectly? Pl try it again.
It should work properly as per your specs, ie the error alert will pop up if
the name entered is a duplicate, and if there is *no* end date (I've just
retested it again here)

Remember it was assumed that end dates are in col C, in C2 down

Take a moment to press the "Yes" button from where you're reading this
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Finding a duplicate entry with no end date
 

Tried it again - and worked perfectly. Thanks so much for your help. J

"Max" wrote:

"Janelle S" wrote:
Thanks Max. One small problem - the error alert pops up if there is an end
date - I want it to pop up if there is no end date. Any suggestions??

That shouldn't be. Perhaps you implemented it incorrectly? Pl try it again.
It should work properly as per your specs, ie the error alert will pop up if
the name entered is a duplicate, and if there is *no* end date (I've just
retested it again here)

Remember it was assumed that end dates are in col C, in C2 down

Take a moment to press the "Yes" button from where you're reading this
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Finding a duplicate entry with no end date
 

Welcome, Janelle
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Janelle S" wrote in message
...
Tried it again - and worked perfectly. Thanks so much for your help. J