can't figure out the formula to use

Hello,

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

A:(NAME)     B:(Agency[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...

0
anonymous (74722)
8/10/2004 4:16:55 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
490 Views

Similar Articles

[PageSpeed] 7

There may be better ways but for your specific example:

=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N-Z"),"")

seems to work.

HTH

Sandy

-- 
to e-mail direct replace @mailintor.com with @tiscali.co.uk

"Katie" <anonymous@discussions.microsoft.com> wrote in message
news:353201c47ef5$74659c40$a301280a@phx.gbl...
> Hello,
>
> I have a SS with 3 columns that look something like this:
>
> A:(NAME)     B:(Agency[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...
>


0
sandymann (252)
8/10/2004 6:21:09 PM
Unfortunately that didn't work.  Any other ideas???

Thanks!
Katie


>-----Original Message-----
>There may be better ways but for your specific example:
>
>=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N-
Z"),"")
>
>seems to work.
>
>HTH
>
>Sandy
>
>-- 
>to e-mail direct replace @mailintor.com with 
@tiscali.co.uk
>
>"Katie" <anonymous@discussions.microsoft.com> wrote in 
message
>news:353201c47ef5$74659c40$a301280a@phx.gbl...
>> Hello,
>>
>> I have a SS with 3 columns that look something like 
this:
>>
>> A:(NAME)     B:(Agency[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...
>>
>
>
>.
>
0
anonymous (74722)
8/10/2004 7:09:48 PM
To clarify, the "Agency1", "Agency2" names were only 
examples.  There are actual agencies that will be in 
there, so I wouldn't be able to look up via the RIGHT 
function...



>-----Original Message-----
>There may be better ways but for your specific example:
>
>=B2&IF(--(RIGHT(B2,1))<3,IF(LEFT(A2,1)<"N"," A-M"," N-
Z"),"")
>
>seems to work.
>
>HTH
>
>Sandy
>
>-- 
>to e-mail direct replace @mailintor.com with 
@tiscali.co.uk
>
>"Katie" <anonymous@discussions.microsoft.com> wrote in 
message
>news:353201c47ef5$74659c40$a301280a@phx.gbl...
>> Hello,
>>
>> I have a SS with 3 columns that look something like 
this:
>>
>> A:(NAME)     B:(Agency[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...
>>
>
>
>.
>
0
anonymous (74722)
8/10/2004 7:17:26 PM
Katie,
Try the following:
=IF(OR(B1="Agency 1",B1="Agenc
2"),IF(CODE(A1)<=77,B1&"A-M",B1&"N-Z"),B1)
Which assumes that the Names are in Column A, the Agency 1/Agency 2
other is in column C and the concatenation formula is in column C
Late add Katie: But all you should need to do to my formula is t
substitute your real agency names for the "agency 1" "agency 2" i

Hope this help

--
Message posted from http://www.ExcelForum.com

0
8/10/2004 7:24:50 PM
Fabulous.  It worked!!!  Thanks!
>-----Original Message-----
>Katie,
>Try the following:
>=IF(OR(B1="Agency 1",B1="Agency
>2"),IF(CODE(A1)<=77,B1&"A-M",B1&"N-Z"),B1)
>Which assumes that the Names are in Column A, the Agency 
1/Agency 2/
>other is in column C and the concatenation formula is in 
column C
>Late add Katie: But all you should need to do to my 
formula is to
>substitute your real agency names for the "agency 
1" "agency 2" i
>
>Hope this helps
>
>
>---
>Message posted from http://www.ExcelForum.com/
>
>.
>
0
anonymous (74722)
8/10/2004 8:14:42 PM
>-----Original Message-----
>Hello,
>
>I have a SS with 3 columns that look something like this:
>
>A:(NAME)     B:(Agency[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...
>
>.Hello,
You are running out on the bottom of the board,so I rush:
(There maybe shorter formulae but no time!)
In C1:

if(and(or(left(a1,1)="A",left(a1,1)="B",.....left(a1,1)
="M"),Mid(B1,1,6)="agency"),"B1&A-M",
if(and(or(left(a1,1)="N",.....left(a1,1)="Z"),Mid(b1,1,6)
="agency")," B1&N-Z","B1"))
if first letter a-m in column a and column B="Agency"
then:"B1&A-M",if first letters in col. A n-z and col b 
first five letters are agency then:B1&N-z else B1.

hope this helps! 
CHECK PARENTHESIS !!!!
>
0
gabelene (6)
8/12/2004 9:03:38 PM
Reply:

Similar Artilces:

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

Formula In Crystal Reports
Hi All, I'm just starting to learn Crystal right now but it's all very new to me so I was just curious if anyone knows if you can do formulas in it. I'd like to take a custom CRM field called setup cost on a product and add all the values of this field up in a quote. Ie if there are 3 products all with seperate setup costs I want to add those together. Or will this have to be done a different way? Thanks all Tom Hi, Crystal supports both VB and Crystal Reports formulas. the help files are quite good regarding formulas, and the 9.2.2 CR with enhanced edition has an excel...

Excel Formula #6
Is there a way to set a formula to calculate how many rows above (COUNT) with no text or numbers in the column? try =COUNTBLANK(F3:F9) -- Don Guillett SalesAid Software donaldb@281.com "deniseS" <dstafiej@dykema.com> wrote in message news:150b101c3c3fa$68119950$a601280a@phx.gbl... > Is there a way to set a formula to calculate how many rows > above (COUNT) with no text or numbers in the column? > =COUNTIF(A1:A100,"") -- HTH. Best wishes Harald Followup to newsgroup only please. "deniseS" <dstafiej@dykema.com> wrote in message news:15...

use a time value in a bar graph
i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Problem using Microsoft Web Browser control
I am trying to access the Custom properties of a Web Browser ActiveX control on a form and I keep getting a message telling me that "The Operation on the Microsoft Web Browser object failed. The OLE server may not be registered. To register the OLE server, reinstall it." I searched TechNet and MSDN and could not resolve this. I reinstalled Access 2003 (I have 2003 and 2007 installed on my workstation, XP Pro) and still no resolution. Has anyone else seen this and if so, can you tell me how to resolve this? Jim Does anyone know the name of the file the message...

Changing my functions to use subtotals?
I have 2 functions that are calculating my data correctly, except now I need to consider the autofilter. I figure using subtotal is the way to go if I want to disregard filtered out rows. Also, I plan on moving my data to another sheet, so it will need to reference the current sheet, which is AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it will ever be over 10,000. I'm using Excel 2003, so I think there's an issue with using something like "A:A" in my formulas. Can someone help me change these to add in a subtotal and take into...

How to Populate .msg File Used in Command Line
I have a .msg file I saved as a boilerplate for Calendar requests that will be programmatically started in an application I am developing. I want to be able to modify the Calendar request's date, time, duration, and message contents based on the user's inputs prior to opening the .msg file. After the user verifies the contents, he/she will click the <Send> button. Currently, from the command line, I can run "c:\program files\microsoft office\office12\outlook.exe" /f "c:\p_review\PR.msg" The Calendar request opens with the boilerplate. Ho...

Formula too long error...
Hello all. I have the following formula in a cell that works just fine. However, I need to add one more SUMIF statement but I get an error stating that the formula is too long. Is there an easy way around this - thank you. NWO =SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E11",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBoth!$S$2:$S$10000<>"110",IF(GoodBoth!$S$2:$S$10000<>"115",1,0))))))+SUM(IF(GoodBoth!$F$2:$F10000="ME",IF(GoodBoth!$R$2:$R10000="E12",IF(GoodBoth!$Q$2:$Q$10000<=20011231,IF(GoodBo...

cleare space using shrink
Hello there I have server which has not enough disk space. I have removed an unessesery data from my databases which is approx 50% of the datbase which is 20 GB As i know i need at least 20 GB on the HDisk to run the shrink (which i don't have, at least 10 GB). how can i free space in this case? ...

Insert Row Under current row (with formulas/formatting)
I've got a sheet that gets appended to regularly but have to do a lot of fiddling to make sure the formatting is correct, calculated fields get added and chart series ranges collect all the data every time I add a new row. The insert (row) command seems to take care of all of this but it inserts the empty row above the current row. As I'm always appending data to the sheet, I would prefer this to be below the current row. I've tried adding a dummy row under all the data containing the formulas and formatting I need but unfortunately I get formula errors and it screws up my chart....

Create Assembly Call
hi All, can the create assembly call under update entity be used to add a certain number of hours/days/months/years to a date. i am trying to do this workflow if variable 1 = yes then create a task update variable 1 sent = yes variable 1 date = execution time can i use the create assembly workflow to add x number of days to variable 1 date and show in variable 2 date. any advice would be helpful please. thanks heaps regards Ridhima If Variable 1 Date is a DateTime field in CRM, then yes. You can use the Add DateTime assembly to take the Value in the DateTime field (which would be t...

Using skins in a multiline edit box
Hi, I have a dialog based application, in which I have a multi-line edit control. In the edit control, I would like to have a background image, say some .bmp file. Some status messages are to be displayed in the edit control, based on certain user actions in the dialog. How do I go about this? Thanks, Sucharit you can do it, i think, if you were to create a class which inherited from the edit control, then in the override the OnDraw()/OnPaint() event, where you first get the rect, and draw your bitmap resource, then call the parent event method to do the rest... I don't know if t...

how to use a saved master page
I have created a new master page and saved it as a .pub file. The question is when opening a new document, how do I apply the saved master file? Open the master file, copy, paste to your new publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Colin" <Colin @discussions.microsoft.com> wrote in message news:A3C59647-7D56-46B9-AA56-C707538ECDED@microsoft.com... >I have created a new master page and saved it as a .pub file. The question > is when opening a new document, how do I apply the saved mas...

How to check if mouse wheel has been used, system-wide?
Hi! I have the following task - I need to check time to time if mouse wheel has been used by the user, in ANY application. I have some kind of user activity detector, to do certain actions in application when user is idle for some time. It would be nice to have the ability to check if mouse wheel has been used. I already know how to check for keyboard (GetKeyboardState and compare with previous call's result) and mouse (GetCursorPos and compare with previous call's result). I know how to set up a hook, and can achieve what I need via hook. But, customer says sometimes system runs slo...

Setup Outlook 2003 to reply using Quotes
I have read some posts already but I am not clear on the quoting/replying method. I have set outlook 2003 as follows: Tools/options/mail - preferences tab - mail options, and then set to prefix with a > for replys and forwards. I get a whole bunch of > on some messages and mostly blue lines on the side for the rest, so I can't do the proper quoting. How is this supposed to be setup? Each time you reply should I cut and paste the text I don't want in the message or is there something that helps do that? If you can reply to this message and use the quoting method as the other g...

Using XSD stored in assembly resource with includes
I am trying to validate an XML file against an XSD that is stored in the assembly as an embedded resource. I can get it to work as long as the XSD does not include other XSDs. After a fair amount of searching, I have found 3 possible solutions but none have worked for me. They are: 1. Use the Includes property of XmlSchema to add included XSDs, then call Compile XmlSchema mainSchema = XmlSchema.Read( stream1, null ); XmlSchema includeSchema1 = XmlSchema.Read( stream2, null ); XmlSchema includeSchema2 = XmlSchema.Read( stream3, null ); mainSchema.Includes.Add( includeSchema1 ...

Limited User can't use Money
I've installed Money 2004 in hopes to clear this same problem I had with 2001, to no avail. I installed Money 04 with my System Administrator acct. It works fine for me. But when my wife tries with her Limited Account, she cannot get access. How do I get her access to this program and data? I've tried installing it in the All Users dir, but no help. I really don't want to give her Sys Admin priveleges just to use Money. Thanks to anyone that can help. MS Support has no info on this, nor does a Google search turn anything up. If she creates a new Money file, can you b...

Formula #2
I am trying ot put in a formula. I have a two columns with Revenue listed. One is the Estimate, one is the actual. If there is not an amount in the Actual amount column, I added the cell that was in the Estimate cell. I color coded the cell because I am breakingit out by Sales Person. I have 3 Sales People. I went in and did the SUM and went into each cell that is colored coded to each person. I am getting an error. Please help. Thanks, Vanessa Not enough information to more than guess at what you might be doing wrong. How are you going into SUM? Are you clicking on the sum icon? ...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Using Code instead of Criteria
In my query builder, I'm getting the message: "The string returned by the builder is too long. The result will be truncated." I think I understand why, as I am using a form to specify a large number of options for the report. Can I use VB to specify these criteria instead of the query builder? Examples in query builder: If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. As ind...

Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two conditions for green, without using two conditions for green is there a way to use OR somehow to beat the limit? Thanks, Norm PS Win2000 + XL2002 Sure ... just click on "Formula Is", and you can use "OR" for more then two conditions. For example: =OR(A1=2,A1=4,A1="good",A1="bad") Will trigger the set format if "any" of the above equate to "True". -- HTH, RD --------------------------------------------------------------------------- Please keep all corr...

Try again: Using mail-to hyperlink with an attachment
I'm creating forms using Excel and Word. Somewhere in the form is an email address. I can use Edit-hyperlink to specify the recipient address of the email address as well as the subject field. Great. What I'd really like is to be able to specify that the form file itself (the ..doc or .xls) is also attached to the email message. I'd like to specify that right in the hyperlink. Then it can act as a workflow. I don't know anything about VBA so I'd rather not go there. Is this possible? Hi Hall, No, I don't think it is possible without some amount of VBA - I c...

Formula involving different sheets
Hi Using Excel 2003. In sheet 1, cell A1: =200/1200 In sheet 2, cell B2: =300/1200 In sheet 3 in a cell, I want to do =200/1200 + 300/1200 by cell reference. How do I do so? Thanks. Your formula would be: =sheet1!a1+sheet2!b2 The best way to create these formulae is to get Excel to do it. 1: Type = into your cell 2: Navigate to your sheet1!a1 cell. You will notice in the formula bar that Excel is adding its address to your formula 3: Type + 4: Navigate to your sheet2!b2 cell. 5: Hit enter Once you learn this, you will never type a cell address again. Regar...

How to clean 0x0E value from file when using XSL?
I have some code that cleans up an xml file before running xsl transformation on it due to what I call bad characters in the data. I have as an example: str = Replace(str, "&#x19;", ".") str = Replace(str, "&#x18;", ".") In there as filters to strip out characters XSL doesn't like and replace it with periods. I am now getting an exception of a hex value of 0x0E however can't figure out what the string replace value should be for this one. From what I can tell, it's some kind of "shift out" character whatever that is. A...