Formula Problem

[Kate]

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) C: (Formula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

 

[Don Guillett]

try this for the 1st letter
=IF(AND(CODE(LEFT(A6))>=CODE("a"),CODE(LEFT(A6))<=CODE("m")),1,2)

 

[Kate]

what does "CODE" mean in the formula? Sorry, a little
confused.

Is that formula for resolving the whole problem? or only
the lookup for the left letter...

Thanks for clarifying
Kate

-----Original Message-----
try this for the 1st letter
=IF(AND(CODE(LEFT(A6))>=CODE("a"),CODE(LEFT(A6))<=CODE ("m")),1,2)

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) C: (Formula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

.

 

[Don Guillett]

It's always nice to try to help yourself first by looking in HELP index for
CODE

--
Don Guillett
SalesAid Software
(e-mail address removed)

what does "CODE" mean in the formula? Sorry, a little
confused.

Is that formula for resolving the whole problem? or only
the lookup for the left letter...

Thanks for clarifying
Kate

-----Original Message-----
try this for the 1st letter
=IF(AND(CODE(LEFT(A6))>=CODE("a"),CODE(LEFT(A6))<=CODE ("m")),1,2)

--
Don Guillett
SalesAid Software
(e-mail address removed)

Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) C: (Formula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...

.

 

[Kate]

tried that, but it doesn't seem that it resolves the
formula problem I'm having.

Any help would be GREATLY appreciated. Thanks...

-----Original Message-----
It's always nice to try to help yourself first by looking in HELP index for
CODE

--
Don Guillett
SalesAid Software
(e-mail address removed)

what does "CODE" mean in the formula? Sorry, a little
confused.

Is that formula for resolving the whole problem? or only
the lookup for the left letter...

Thanks for clarifying
Kate

-----Original Message-----
try this for the 1st letter
=IF(AND(CODE(LEFT(A6))>=CODE("a"),CODE(LEFT(A6))<=CODE ("m")),1,2)

--
Don Guillett
SalesAid Software
(e-mail address removed)
Hello,

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) C: (Formula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-

M"'.
if

the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

I appreciate the help. Thanks...


.

.

 

[Harlan Grove]

I have a SS with 3 columns that look something like this:

ANAME) BAgency[Vlookup Result]) C: (Formula Result)
Jones, Joe Agency1 Agency1 A-M
Smith, Bob Agency2 Agency2 N-Z
Kid, Gregg Agency3 Agency3
Rohl, Jim Agency4 Agency4

**column B is a result of another sheet in the workbook...

I am trying to find a formula that would follow this logic:

if the 1st letter in A1 is between A-M, and B1
is "Agency1" or "Agency2", then C1 will be 'B1&"A-M"'. if
the first letter in A1 is between N-Z, and B1 is "Agency1"
or "Agency2", then C1 will be 'B1&"N-Z"'. If B1 equals
anything BUT "Agency1" or "Agency2", then the result will
only be 'B1'.

C1:
=B1&IF(OR(B1={"Agency1","Agency2"}),IF(A1<"N"," A-M"," N-Z"),"")