Data Validation or VBA

[JSnow]

I would like to 'police' some of the content my users enter. They're
supposed to enter names thusly: Last, First. However, some are entering
First Last.

Is there a way to have an alert pop-up if no comma is detected in the cell?
Excel's help notes on data validation are surpisingly thin.

I'm using column C starting with row 3 and continuing till 200 or son.

Thanks gang.

 

[Dave Peterson]

You could use data|validation with a custom formula like:

=ISNUMBER(SEARCH(",",A1))

But what about Cher and Madonna? <vbg>

If I were setting this up, I would use two (or more) columns. One dedicated to
the first name and one dedicated to the last name.

It could make further processing much more simple.

 

[Kevin B]

It would be easier for you to have the first name and last name in separate
columns. You can always concatenate them in any order you want and sorting
by last name produces the correct result.

If A1 has the first name Fritz and B1 has the last name of Abercrombie you
could concatenate last name, comma, first name with the following formula:

=B1&", "&A1

 

[JSnow]

Thanks for the formula, Dave. I prolly should have used two columns but
we've already got over 250 rows of data entered, and i'm not starting over on
this.

I'll give this a try.

 

[David Biddulph]

You can split your existing data with Data/ Text to Columns/ Delimited:
Comma

 

[JSnow]

Dave, your formula worked just fine. Here's a new wrinkle for you: can I add
to the forumal by using OR? Or is there any way to have a second condition
to the data validation?

Now that I'm forcing users to enter Last, First they should also enter data

 

[Dave Peterson]

I think you're making a big mistake by not following David's suggestion of
splitting the names into separate fields (using data|text to columns).

The longer you wait, the more you'll have to fix later.

And I think it's a big mistake to put your data in multiple sheets. You can't
sort it easily, you can't filter easily, you can't use mailmerge easily.

But if you want...

Debra Dalgleish shares the formula to return the sheet name (if the workbook has
been saved at least once) here:
http://contextures.com/xlfaqFun.html#SheetName

Using her formula, this worked ok for me:

=AND(ISNUMBER(SEARCH(",",A1)),
CODE(UPPER(LEFT(A1,1)))>=CODE(UPPER(LEFT(MID(CELL("filename",A1),
FIND("]",CELL("filename",A1))+1,255),1))),
CODE(UPPER(LEFT(A1,1)))<=CODE(UPPER(RIGHT(MID(CELL("filename",A1),
FIND("]",CELL("filename",A1))+1,255),1))))

If you decide to put your data into a single worksheet, you may want to see
another tip from Debra:
http://contextures.com/xlautofilter02.html#Limits

You can use that to filter based on the leading character of the field.