Finding a duplicate entry with no end date

[Janelle S]

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.

 

[Mike H]

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

 

[Mike H]

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","")

 

[Max]

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 ..

 

[Janelle S]

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]

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

---

 

[Janelle S]

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

 

[Max]

Welcome, Janelle