Data Validation or VBA
 

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.

Data Validation or VBA
 

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.

JSnow wrote:

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

Data Validation or VBA
 

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
--
Kevin Backmann


"JSnow" wrote:

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.

Data Validation or VBA
 

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.

"Dave Peterson" wrote:

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.

JSnow wrote:

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

Data Validation or VBA
 

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


"JSnow" wrote in message
...
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.

"Dave Peterson" wrote:

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.

JSnow wrote:

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

Data Validation or VBA
 

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
on the correct page. Page A-G, H-Q, and R-Z. If someone entered 'James,
Jesse' on R-Z can there be an alert saying wrong page?

"Dave Peterson" wrote:

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.

JSnow wrote:

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

Data Validation or VBA
 

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

JSnow wrote:

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
on the correct page. Page A-G, H-Q, and R-Z. If someone entered 'James,
Jesse' on R-Z can there be an alert saying wrong page?

"Dave Peterson" wrote:

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.

JSnow wrote:

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


--

Dave Peterson