IF Function Problem

Hello All,

I am using Office XP and have the following problem

I have a worksheet which uses lookup to extract names from range name
Codes as follows

Col
O=IF(ISNA(VLOOKUP(D2,CODES,6,FALSE)),"",(VLOOKUP(D2,CODES,6,FALSE)))


I wish to add another IF condition to the above formula to test that:
If Col D2 = EGSP AND Col A = date greater than or equal to 15-Nov-2005
then it should display the name as 'Jack' otherwise 'James'.

All other values should remain unchanged

Thanks a lot

Rashid

0
prkhan561 (26)
11/16/2005 7:27:00 AM
excel 39879 articles. 2 followers. Follow

2 Replies
729 Views

Similar Articles

[PageSpeed] 57

=IF(D2="EGSP",IF(A2>=--"2005-11-15","Jack","James"),IF(ISNA(VLOOKUP(D2,CODES
,6,FALSE)),"",(VLOOKUP(D2,CODES,6,FALSE))))

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


<prkhan56@gmail.com> wrote in message
news:1132126020.839890.269890@g43g2000cwa.googlegroups.com...
> Hello All,
>
> I am using Office XP and have the following problem
>
> I have a worksheet which uses lookup to extract names from range name
> Codes as follows
>
> Col
> O=IF(ISNA(VLOOKUP(D2,CODES,6,FALSE)),"",(VLOOKUP(D2,CODES,6,FALSE)))
>
>
> I wish to add another IF condition to the above formula to test that:
> If Col D2 = EGSP AND Col A = date greater than or equal to 15-Nov-2005
> then it should display the name as 'Jack' otherwise 'James'.
>
> All other values should remain unchanged
>
> Thanks a lot
>
> Rashid
>


0
bob.phillips1 (6510)
11/16/2005 8:27:39 AM
Hello Bob,
Sorry for the delay.. it works fine.. Thanks a lot

Rashid

0
prkhan561 (26)
11/21/2005 10:25:48 AM
Reply:

Similar Artilces:

Microsoft Money 2007 Archiving Problem
Windows XP SP3 Microsoft Money 2007 Deluxe I am trying to archive my 2007 records as I have done every year. This year I finally got my 401(k) into Money. The problem is when I ask Money to archive to clear out 2007 the list of accounts to archive does not list my 401(k) at all. So I select all accounts I can and then in the cleaner file with only 2008 in it all of my paychecks from 2007 still remain in my register (I'm assuming because the 401(k) is still attached to them and if they disappear things won't balance). Is there something I can do so that this will not happen. As mu...

ROUND Function with other function
Hi, I have this formula: =IF(R8>0,CONCATENATE("+",R8),R8) and the results shows me for example: +11.6 but what I really want t show is +12. I've been trying to put the "Round" function at a few places in th formula but it doesn't work. The closest I got was with this formula: =ROUND(IF(R8>0,CONCATENATE("+",R8),R8),0) where it showed me 12, without the "+" sign before.... Can anyone help me ? Thank -- Outapi ----------------------------------------------------------------------- Outapin's Profile: http://www.excelforum.com/member...

problem with chevron menu
dear all. I am having a toolbar, that has some chevrons attached to one button. Now I would like to add some functions to each of the chevrons, but I don't know how. Here comes the code, that I have implemented so far (in MainFrm): BEGIN_MESSAGE_MAP(CMainFrame, CMDIFrameWnd) //{{AFX_MSG_MAP(CMainFrame) // NOTE - the ClassWizard will add and remove mapping macros here. // DO NOT EDIT what you see in these blocks of generated code ! ON_WM_CREATE() ON_COMMAND(IDB_NN,OnButtoncheck) //}}AFX_MSG_MAP END_MESSAGE_MAP() //... int CMainFrame::OnCreate(LPCREATESTRUCT lpCreateStruct) {...

Performance problem discovered
Sorry if this message appears twice, I got no confirmation if it was posted. I believe that I have discovered a significant performance problem on the client side of CRM 3.0. On a single CPU machine, with no hyper-threading, if you click on Accounts and Contact on the left hand side (and you have a fair number of active accounts and contacts), then the local machine CPU usage will be 100% for 5+ seconds for most of the time (except the first few clicks). On a dual CPU / hyper-threaded CPU this is not so. SInce the speed difference is about a factor of 100, obviously its not because i...

Returning a range from an inner function
I have a range of data, which has had the DOLLAR() function performed on it. I would like to get the total of this range using the SUM() function. doing SUM(F1:F4) will not work, so I need to convert the data into values that will work. The VALUE() function is perfect for converting the data into values that will work for SUM(), but the formula SUM(VALUE(F1:F4)) does not work because VALUE() will not accept a range as a parameter (and also does not return a range). I need to perform the VALUE() function on each cell before submitting it to SUM(). There must be some way to do this, cons...

JustifyString function problem
Hi, I'm using the "JustifyString" function (from Stephan Lebans) to right justify three columns in a ListBox. Two of them are working fine ... one's a simple number (long integer) and the other's currency. The third causes an Access 2003 crash! That third column is a long integer which is a link to the autonumber field in another table. Both tables are in the query as I need columns from both tables in the ListBox. If I change the JustifyString to reference the autonumber field in the other table, I still get an Access crash. I didn't review the Access er...

More validadtion problems!
Hello All I have a worksheet that will be used for simple data entry of name, address, etc etc. - 1 column per field. One of the columns is for National Insurance number, and one is for postcode (UK), and I am struggling to get the validation for either of these. National insurance number: This must be in the format AA000000A (i.e. two letters followed by 6 numerics followed by one letter) - BUT only certain combinations of the first two letters are valid (e.g. WD is OK but WR is not), and only certain letters at the end are valid: I have a list of the valid combinations of first two ...

automating functions
I am a complete novice at CRM 1.2, but want to be able to create, for example, a workflow process that will monitor the CSR's and the queues and automatically assign a new case to the least busy CSR. Any input would be appreciated. Thanks in advance Jim Please tell me what I would have to do and with what tools in order to create such a workflow process. "jmb" wrote: > I am a complete novice at CRM 1.2, but want to be able to create, for > example, a workflow process that will monitor the CSR's and the queues and > automatically assign a new case to the lea...

Problems with Free/Busy data after server crash
Hi, Here's a description of my problem. Any ideas of how to get around it would be greatly appreciated! I'm running Exchange 5.5 in an NT4 domain. All latest service packs and updates are installed. ExchangeA has been the sole exchange server for a number of years. A couple of weeks ago it started developing major problems so I installed a second server (ExchangeB) so I could move everything accross. This was set to be in the same site as ExchangeA. They replicated without a problem (or at least that's what I first thought) and all users were migrated to ExchangeB with the view ...

Returning a CHAR[] from a function
I have the following function that returns the path of where the program was run from. However I cant seem to return the butter as a CHAR. The code works if I use it in a procedure but not where I need to return the variable CHAR buffer. char CAutoRunDlg::GetProgramPath(void) { char buffer[_MAX_PATH]; //declares maimum path //opens window maximized //_getcwd(,)gets the current working directory // #include <direct.h> //needed for current working directory /* Get the current working directory: */ if( _getcwd( buffer, _MAX_PATH ) == NULL ) perror( "_getcwd error" )...

Complex functions
Hi All, Suppose you have a very complex and long function that is also subject to a condition, then you will/can have something like: =if(condition(complex function), complex function, other complex function) It can have more lines in the formula bar, so readability below 0 Kelvin. Is there a way to 'name' a function? =If(condition(myfunc = complex function), myfunc, other complex function) With kind regards, JP Yes you can put a formula into an Excel Name (the same as you can give a range an Excel Name. Very powerful it can be too. Do a Google fo...

Archive problems with Money 2002
I have searched through this forum for topics related to archiving and I did not see one with my particular issue. the general gist of the posts that I have seen, however, is to avoid archiving. That said, I'm hoping that someone has a better answer. I will try to be brief: When I tried to archive my transactions today, I noted immediately that my bank account balance went to negative $ 15k. Several of my credit card accounts were also badly messed up. Upon further research, it seems that any transaction associated to the cash portion of my brokerage account was not being ar...

display problem
I use excel 97 in windows xp professional edition. I have a display problem in excel: When the cell is justified to the right, the contents of cell is truncated by the gridline, both on the screen and while printing it. The problem does not occur in excel 97 in windows 98. Does anyone have an idea how to solve this? ...

IF Statement Problem
I am having trouble with Excel not returning a blank in an IF statement. My spreadsheet has three tabs. The first tab is a list of tracking numbers that need checked (12 digits). The second tab is a list of tracking numbers (16 digits) that are scanned into the spreadsheet in column A as we receive packages. In column B rows 2 through 600 is the formula =left(a2,12) - adjusted for each row. The third tab reconciles the two lists and has the following formula: Column A - =IF('Sheet1'!A2="","",'Sheet1'!A2) Column B - =IF(A2=""...

Turning Off Error Reporting using API Functions and VB
We want to automatically turn off the error reporting options using Windows API functions for the following functions area Click on Start, Settings, Control Panel, System, the Advanced tab and Error Reporting (at the bottom of the screen), which launches the Error Reporting screen. Another way to get to that screen: Open My Computer, right-click on Properties, click on Advanced tab and Error Reporting. Any assistance would be welcome. It's not a program's call. It's the user's decision. --=20 -------------------------------------------------------------------------= --...

CWizard97PropertyPage/Sheet Problems...
Using Microsoft's CWizard97PropertyPage/Sheet code, I created a Wizard with 4 pages. On page 2 of them is a dialog with 3 Radio Buttons. While on page 2, the CWizard97PropertyPage2::DoDataExchange() function optionally grays out one of them and checks another. The code GetCheckedRadioButton() works just fine in getting the current state of the radio buttons from that function. The problem is this: I NEED TO CHECK WHICH RADIO BUTTON THE USER CLICKED! I've researched this problem thoroughly and am completely stumped. GetCheckedRadioButton() doesn't work from page 3 to tell m...

Problem going "Online" after going "offline" with laptop Client
Hi! I have a user who uses the laptop client of CRM and when he clicks "Go Offline" in Outlook, it syncs down everything he owns from crm without issue, and then works fine disconnected. However the problem is when he plugs back into the network and wants to "Go Online". When he clicks the Go Online button it says something along the lines of CRM could not find or could not communicate with the CRM server. I fixed it once thinking it was a fluke by uninstalling the MSDE (and db) and the laptop client and reinstalling it all (a rather long process), but when he did it ag...

Match Function
Hi, I have put together a spreadsheet to find codes, states, descriptions. It finds the data by dates. Columns are: Dates, Codes, States, Descriptions If I put in a date it matches the code, state and description. My problem is that if I have 2 dates that are the same it will only match the first date it can find. What kind of formula do I need so that it will output the second or third date data. Thanks for your help. If you need a copy of the spreadsheet please let me know. How many rows of data do you have? -- Biff Microsoft Excel MVP "Melanie" <Melanie@discussio...

Report Formatting Problems
We're trying to design our invoices to fit our preprinted forms (we would do it the other way around, but we have 10 cases of invoice paper left!) and I'm having difficulty with Report Writer. I'm wondering if someone can help: 1) Is it possible to change the font of a field OTHER then changing the DPI of the whole report? 2) Is it possible to wrap a field, such as the Long Description field, so that it will fit in a fixed width column? I am perplexed that there is not more functionality in the report design in a Microsoft product such as this. Thanks for any tips y...

format cell problem
Hi, I do not arrive to create this personalized format who is responding to these 3 conditions (number is displayed in percentage). 1) if < 0 the colour must be red 2) if < 10 the value must be replaced by "NS" 3) if > 10 then value must be replace by "NS" the only think have found is : [<+10]"NS";[Red]-0,0%;0,0% with this format steps1,3 are ok but impossible to integrate step 2 is anybody can help me !! it's important for me.. thanks in advance Alex One way: [Red][<=0]0.0%;"NS" Note that you didn't specify what sh...

Language Problems
Version: 2008 Operating System: Mac OS X 10.4 (Tiger) Processor: intel Hi new here and getting frustrated trying to figure this out by myself. I was quite proud that i resolved my word spellcheck problems by changing my default language and changing the system Input menu keyboard and language settings thanks to help and advice i found on this site. My issue is that when i open a resume template from the Microsoft Project Gallery it is not in English, i think it is Spanish. I have spent hours trying to figure out why this happens but i cant find too much info out there reguarding project gall...

If Function Function
okay. So I am trying to workout a formula to compute a commision earning. Basically, if X amount of new business is made, then X*Y (corresponding Commision rate percentage), but if new business =<X, then X*Y (Commision rate percentage that is true). Problem is, it's super confusing...anyone have any suggestions? See if this helps: http://mcgimpsey.com/excel/variablerate.html -- Biff Microsoft Excel MVP "aetejada" <aetejada@discussions.microsoft.com> wrote in message news:AA4E55F7-9435-4BB6-837A-416A59BB72A0@microsoft.com... > okay. So I am...

MS CRM 3.0 Customer Case Problem
Dear CRM Experts, I have a problem with MS CRM 3.0: I want to create a web based form in our company website that customers create their Cases (tickets) by it and we (I and my Tech. staff) response to them (Ticketing system). It seems for security reasons we shouldn’t allow CRM browsing outside of our Internal Network (Intranet). Please help me how can present solution for this problem. Best Regards, Mohsen Ahmadi You have a couple of options. There are third-party apps that will let you integrate CRM with your website, but with 3.0 you would probably need to purchase the External C...

Date and time / separate
I have one field in the table: startdateandtime I have 3 textboxes on the form: txtstartdateandtime txtstartdate txtstarttime txtstartdateandtime is bound to the table field and is not visible. txtstartdate and txtstarttime are meant to make it easier for the user than a single field would be. On the AfterUpdate events of both txtstartdate and txtstarttime I use the following: Me.txtStartdateandtime = CDate(Nz(Me.txtStartdate, 0)) + TimeValue(Nz(Me.txtStarttime, "00:00:00")) Me.Repaint to put the entered values into the bound field (and then into the table). This works. B...

Outlook 2002 Change Password function
I want users to be able to change password from the Outlook Login Page, but it does noet seem to work. Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 Important: Outlook client is not member of the domain... There are not many Microsoft Articles about this. Anyone? -- Regards, Menko den Ouden Netherlands Menko den Ouden schreef in deze nieuwsgroep op 23-6-2005 15:28: > I want users to be able to change password from the Outlook Login Page, but > it does noet seem to work. > > Outlook 2002 Sp1 and Windows Server 2003 (AD) and Exchange 2003 SP1 > ...