If Statement 11-20-09

Hello,

I asked this question a couple of days ago but I don't think that I stated 
it well enough as the formula that was given to me was not quite what I 
needed.
I'll try again.

In my spread sheet in cell D3 is a date, lets say 10/22/2009

In Column A from Cell A18 thru A32 are other dates.  I need a formula that 
will check A18 thru A32 against D3 and if the date matchs I need to populate 
that respective cell (A whatever) with effective date or if a match is not 
found I need to populate the cell with not effective.  
So if A19 matches I need effective date in that cell, if A25 doesn't match I 
need not effective in that cell. 
Thanks in advance for any help you can give me.
0
Utf
11/20/2009 6:19:02 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
702 Views

Similar Articles

[PageSpeed] 31

I think you need to use a separate column. What you've described is 
*replacing* the date in column A with either "effective date" or "not 
effective".

Enter this formula in B18 and copy down to B32:

=IF(A18=D$3,"Effective Date","Not Effective")

-- 
Biff
Microsoft Excel MVP


"Dianne" <Dianne@discussions.microsoft.com> wrote in message 
news:130F4213-A76C-4D5A-A062-5FEF136755CB@microsoft.com...
> Hello,
>
> I asked this question a couple of days ago but I don't think that I stated
> it well enough as the formula that was given to me was not quite what I
> needed.
> I'll try again.
>
> In my spread sheet in cell D3 is a date, lets say 10/22/2009
>
> In Column A from Cell A18 thru A32 are other dates.  I need a formula that
> will check A18 thru A32 against D3 and if the date matchs I need to 
> populate
> that respective cell (A whatever) with effective date or if a match is not
> found I need to populate the cell with not effective.
> So if A19 matches I need effective date in that cell, if A25 doesn't match 
> I
> need not effective in that cell.
> Thanks in advance for any help you can give me. 


0
T
11/20/2009 6:30:43 PM
The way you wrote that, you are overwriting the date/date that was already in 
the A column with new information? Is that really what you want? I will 
assume that you actually want the new information in B column.

In B18:
=IF(A18=D$3,"Effective Date","Not Effective")

Guessing again, you might want:
=IF(A18<=D$3,"Effective Date","Not Effective")

if you actually are checking for dates that have already earlier than the 
date in D3. Copy down the formulas as needed.
-- 
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Dianne" wrote:

> Hello,
> 
> I asked this question a couple of days ago but I don't think that I stated 
> it well enough as the formula that was given to me was not quite what I 
> needed.
> I'll try again.
> 
> In my spread sheet in cell D3 is a date, lets say 10/22/2009
> 
> In Column A from Cell A18 thru A32 are other dates.  I need a formula that 
> will check A18 thru A32 against D3 and if the date matchs I need to populate 
> that respective cell (A whatever) with effective date or if a match is not 
> found I need to populate the cell with not effective.  
> So if A19 matches I need effective date in that cell, if A25 doesn't match I 
> need not effective in that cell. 
> Thanks in advance for any help you can give me.
0
Utf
11/20/2009 6:46:02 PM
Reply:

Similar Artilces:

Unhandled script exception #11
Hi, When opening an item from the Item Maintenance window, i get the following message: Unhandled script exception: set precision passed bad precision value -1. EXCEPTION_CLASS_SCRIPT_OUT_OF_RANGE SCRIPT_CMD_SETPRECISION This error is only hapenning on a few items out of thousands, any ideas why and how to solve? Thanks Jeff Dynamics will attempt to set the precision of a few fields based on the number of decimal places for the currency, and in the case of the quantities the number of decimal places for the quantities. It occurs to me that fo...

can't send email 09-02-10
still can't send email, but can recieve it. i have checked my servers and re-set my account.this just started happening today and when i get the pop up to enter my user name and password i press cancel and get the box that shows the error but the box is blank.it seems lots of people are having this problem so is it a windows mail problem? i've never had this problem before today. First, test your password by logging in to your provider's webmail. If that works, it proves you have the correct username and password. A likely reason for the username/password failing in W...

Or, index, match statements
I have a workbook with 8 workheets. The first 7 worksheet are name Mon, Tues, Wed, Thur, Fri, Sat, Sun. These 7 contain the name my employees and whether they show up to work in text statement. They all look like these. Mon Worksheet A1 B1 Name Tom Jason not show up to work Susan not Show up to work Ryan Bill not Show up to work Notice that if any of my 5 employees show up to work, cell in column B is leaving blank Similarly for Tues A1 B1 Name Tom Jason not show up to work Susan not Show up to...

Registers not showing download statements
I've downloaded two statements from Bank of America and Capital One Services to my system. I opened Money, went to the appropriate register, opened it, then went to FILE | IMPORT | selected the correct downloaded statement | clicked IMPORT | and received the message that the import was successful. Returning to the register, it now shows in the left-hand column "Downloaded" that I have 6 items that need to be reviewed, however, there is nothing in the register - nothing was added, nothing is visable. The same problem arose in the other account register where it now sh...

SAGE line 50 v 11 and VB.NET
Hi Folks, I am building an application in VB.NET... It connects to 2 databases MS SQL SERVER 2005 and MS ACCESS 2003 .. It works all fine with SQL but It only reads the record from the ACCESS table which is linked with SAGE ACCOUNTING SOFTWARE V 11... Now When i Update or add record to the ACCESS linked table then i got the Error..... INVALID KEYWORD SPECIFICATION... My Code looks like. ... ' ---------------------------------------------------------------------------- ------------------------------------------------------------------- Dim connectiontring As String = "Provider=Micro...

Microsoft Dynamics CRM 3.0 Data Migration Pack 09-11-07
I'm trying to migrate data from Microsoft BCM 2007 to CRM Professional 3.0. I have CRM installed on one server with its SQL database on a separate SQL Server Standard 2005 server. CRM is installed and working properly. The "Microsoft Dynamics CRM 3.0 Data Migration Pack" does not work. Is it because SQL and CRM are on different servers? (Seemed odd that the readme has me attach the BCM DB and then the app asks me where the file is. Shouldn't it be asking me for a SQL server and DB, not a MDF file?) Or, is it because the BCM is from office 2007? Any help getting over thi...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

Workflow Manager not working 08-11-04
All CRM Services are running and i try with LocalHost, Ip Address, http://<name of server>, etc. and not working and show me the same error. my user is a Domain Admin and a System Administrator in CRM with license professional any ideas? >>-----Original Message----- >>He's right. Just type the IP of your crm server, or >localhost . >> >>"Qureshi" wrote: >> >>> Hi Edgar, >>> I hope when you mentioned, CRM Services are running you >checked, Microsoft >>> CRM Workfolw Service. You can access CRM with this...

Conditional Format #11
Hi I have a cell that =today(). Then, I have a column of due dates fo rows. I would like to set up a conditional format that turns the du date cells green, yellow, or red if they are 3,2,or 1 week(s) out. An ideas? Thank -- Message posted from http://www.ExcelForum.com I've done something like this before where I used =IF(A4="","",IF(D4<TODAY(),"Late","Open")) to enter a descriptive word into one of the cells of my spreadsheet then used Cell Value is equal to ="Late" in the conditional formatting to tur the cell red with white...

SQL Statement trying to call column not specified in statement
I'm having some trouble with a sql statement within my sub. For some reason, it wants to reference CLPROPSTRTNMBER when I specifically asked for CLPROPSTRTNMBR (which is the correct column name). The code is below. I cannot figure out why it's trying to pull this reference. Public Sub D2REDataCall() Call DBConnect sql = "SELECT CLMAILADDRESS,CLMAILCITY,CLMAILSTATE,CLMAILZIP,CLPROPSTRTNMBR,CLPROPSTREETADDR,CLPROPSTREETUNIT,CLPROPCITY,CLPROPSTATE,CLPROPZIP,CLPROPCOUNTY,CLCREDITREPORTDT,CLRATEEFFECTIVEDT,CLCLOSEDT,CLTOTALLOANAMT,CLINTERESTRATE,CLTILAPR,CLTERM FROM " & tblC...

migration #11
We rcently bought a NEW SERVER where i installed exchange 2003 and attempted to migrate from the OLD EXCHANGE SERVER (WINDOWS 2000 SERVER, EXCHANGE 2000). After installing Exchnage 2003 oin the NEW server, i moved all the mailboxes from the OLD SERVER to the NEW one and I set the NEW one as Primary. However, due to some problems in the IIS on the NEW SERVER i decided to wipe the NEW SERVER and re-install IIS and Excahnge Server 2003 . Therefore I moved all the mailboxes back to the OLD SERVER and set the OLD as the Primary again. Unfortunately after moving the mailboxes, certan user...

if
I'm looking to make excel automatically do a find-replace. I could go through one sheet, do a find on each item in column A, and replace on each number in column A by hand, but I figured there would be an easier more fail-safe way to have excel automatically do what I'm doing by hand. The end result is to find out what numbers are left over after replacing all duplicate numbers. leroux.charles@gmail.com Wrote: > I'm looking to make excel automatically do a find-replace. > I could go through one sheet, do a find on each item in column A, and > replace on each number in...

Nesting IF statements, Conditional Formatting
Hi Folks, I'm hoping someone has an easy answer. I have a dillemma I wanted to address with conditional formatting but the conditional formatting only allows for 3 circumstances. I have a column of percentages - 0 through 100%. I have a series of ranks - A through F and X. A = >0 through 20% B = 21 through 40% C = 41 through 60% D = 61 through 80% F = 81 through 100% X = 0% If the percentages are in column I, and I want column J to reflect the proper letter rank, based on I, how do I nest my formula in J to show the right information, *or* how do I use conditional formatting? I in...

Task Usage View 12-19-09
Hi, I currently have a Task Usage View that displays information up to 2049. My project end date is 2017. How can I customize the view to only show data up to 2017? I have a lot of blank on the right side and that makes the printout out the view difficult. Thanks DodoMSP Have you tried clicking in the table on the right, holding down the ctrl key and scrolling with the mouse wheel? The magnifiying glass command will do the same. ....or did I misunderstand your question? - Andrew Lavinsky Blog: http://blogs.catapultsystems.com/epm > Hi, > > I curre...

Shared Account 11-09-06
Hi, We have shared an couple of accounts for some users in the sales. We would like to generate a report based on that. where could i locate this information from, i searched the filtered views but could not find it -sathish ...

Can't open database 09-09-03
Hi, I get the following message on trying to open my database; 'This database is in an unexpected state;Microsoft Access can't open it.' This happened after apply server patch 3 for Windows 2000. I can't convert or repair the database from the Tools menu. Nor can I import the tables into another database. Anyone have any ideas or suggestions? Thanks, Keith ...

Options for large If/ElseIf statement
I’m trying to add value to a form control(Contract Number) using the data from a table “Contract” which has 3 fields ContractShipline, ContractCustomer, ContractNumber. I’m currently using a very long “If / ElseIf ” statement in the form to fill in the Contract Number when Shipline and ContractCustomer are equal . If [cboAsiaNoticeShipLine] = "FE Evergreen" And [cboAsiaNoticeServiceContractCustomer] = "CELSUS EXPORT LLC." Then [AsiaNoticeContractNumber] = "35378" Because the contract numbers change a lot this has become high maintenance . My thou...

Macro to find and delete all FALSE statements
So i have a sheet filled with formulas and I have put IF,Then statements in them to return FALSE is I don't get the result I want. However, I need to erase all these FALSE results so they do not plot as zero on my chart. Anyway to create a macro? Thanks. You may not need a macro. If you formulas are of the type =IF(A1<10,FALSE,A1), select the range and use Edit | Replace to change FALSE to NA(). The NA() will show in cell as #N/A and will be ignored by the chart. best wishes -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in email address "Woody13" &l...

Conditional formatting #20
Hello I want to set the 'fill' colour of a cell according to the data in that cell, so if the cell contains 1 it is filled red, 2 is blue, etc etc. No problem, I can do this with conditional formatting - format the cell's fill colour. I then want to make the underlying data itself invisible. No problem, I use conditional formatting to make the data font colour the same as the fill colour. But what if I want to add a gradient or pattern to the cell's fill colour ? I cannot find how to make the cell data invisible when I use a pattern or gradient for the cells 'fill...

table lookup 05-09-10
Let's say i have a table. I can use MATCH() to know that I want to look in column 13 for a certain value. Now I just need to figure out what row a certain value is. For instance: 1810. Somewhere in Column 13 is the value "1810"....I would like to find out what ROW it's in. How can I tell Excel to look in Column 13 and find 1810, and let me know what row it's in? DK The function you need is MATCH. -- David Biddulph "dksaluki" <dksaluki@gmail.com> wrote in message news:efb80a83-a0fa-4bcf-9084-7275d4901807@o14g2000yqb.googlegroups.com...

OR statements
What is the maximum number of arguments you can use in one (Excel 2003). Is this maximum lower in a conditional format? -- Paul Hyett, Cheltenham (change 'invalid83261' to 'blueyonder' to email me) OR - maximum number of arguments: Help file says 30 Probably the same in CF. Also, you can group the arguments and effectively get more. That many arguments however, could produce a very ugly formula. <g> -- Jim Cone Portland, Oregon USA http://www.mediafire.com/PrimitiveSoftware .. .. .. "Paul Hyett" <vidcapper@invalid83261.co.uk> wrote in message ...

Coding for IIf Statement
Can anyone tell me what is wrong with this statement? =IIf(DataEntryFrm![Waive10%]=True,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved],IIf(DataEntryFrm![Waive10%]=False,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Granted])) When I run this I get #Name? in the textbox. Assuming that the form that contains the textbox with this control source expression is named DataEntryFrm: =IIf([Waive10%]=True,ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Approved],ItemSubFrm.Form![Total$ofItemsRequestedSubfrm].Form![Total Granted]) -- Ken S...

update record 07-09-03
If the text in the combo box it work, if I add a new record and is not in the combo box it work, but can not update a record in the table, it adds a new record in the table. (using the NotInList event), any time a value is entered into the combobox that does not exist in the combobox but in the table why is going to be added Please let me what I am doing wrong: This is what I want: 1. If the text is in the combo box,update record in the table 2. If the text is not in combo box, and not in table then add a new record in the table. 3. If the text is not in the combo box, but in the...

Microsoft Outlook 03-11-10
I have just got a message saying not installed for the current user and it will not load. But I've been using it for years. George George;131443 Wrote: > I have just got a message saying not installed for the current user and > it > will not load. But I've been using it for years. > > GeorgeGeorge, welcome to the forum. When you post (here or in any forum), you will get faster and less frustrating replies if you do a few standard things: - try to define the problem in the thread title. Since this is an Outlook forum, simply stating "Mic...

need help with an if statement
right now i am using this code on a command button; Sub Save_As_FileName() FName1 = Range("d3").Value FName2 = Range("d5").Value Fname3 = Range("d6").Value Fname4 = Range("d7").Value pth = "f:\bids\" MyFileName = FName1 & " " & FName2 & " " & Fname3 & " " & Fname & " " & ".xls" ActiveWorkbook.SaveAs Filename:=pth & MyFileName End Sub But the pth will change, depending on what is in cell d2. so, I think i will need an If statement saying if cell d2 = ...