to lookup data for 'today()'

e.g

             A                     B                      C                  
     D
1    Date (From)      01 Jan 2009      01 Feb 2009       01 Mar 2009
2    Date (To)          31 Jan 2009      28 Feb 2009       31 Mar 2009
3    Actual Qty               205                  217                    300
4    Target Qty              180                  250                     
401       


if today = 15 Feb 2009, how to get actual qty using excel formula?
0
Utf
3/30/2010 12:45:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
744 Views

Similar Articles

[PageSpeed] 34

Hi,

=SUMPRODUCT(--(TODAY()>=A1:H1),--(TODAY()<=A2:H2),A3:H3)

"nordiyu" wrote:

> e.g
> 
>              A                     B                      C                  
>      D
> 1    Date (From)      01 Jan 2009      01 Feb 2009       01 Mar 2009
> 2    Date (To)          31 Jan 2009      28 Feb 2009       31 Mar 2009
> 3    Actual Qty               205                  217                    300
> 4    Target Qty              180                  250                     
> 401       
> 
> 
> if today = 15 Feb 2009, how to get actual qty using excel formula?
0
Utf
3/30/2010 12:57:01 PM
Put the test date (15-Feb-2009) in  a cell, I used A7
In another cell enter =INDEX(B3:M3,MONTH(A7))
This will return 217
best wishes
-- 
Bernard Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme

"nordiyu" <nordiyu@discussions.microsoft.com> wrote in message 
news:CAD0A58E-9BAE-4C4A-BD09-1C1AB72B922D@microsoft.com...
> e.g
>
>             A                     B                      C
>     D
> 1    Date (From)      01 Jan 2009      01 Feb 2009       01 Mar 2009
> 2    Date (To)          31 Jan 2009      28 Feb 2009       31 Mar 2009
> 3    Actual Qty               205                  217 
> 300
> 4    Target Qty              180                  250
> 401
>
>
> if today = 15 Feb 2009, how to get actual qty using excel formula? 

0
Bernard
3/30/2010 1:10:33 PM
Reply:

Similar Artilces:

Combo box change only if other text boxes have data
I have a combo box on a form that has three status options - Open, Closed - NG and Closed - OK. All records in the form are Open but the user can close them only if certain conditions are met. For example, the record status can be Closed - OK only if Countermeasures and a body number are noted (two separate text boxes). A record status can only be Closed - NG if comments (separate text box)are give as to why no countermeasure can be found. How can I prevent the combo box change based on these criteria? On Mon, 3 Dec 2007 19:15:14 -0800 (PST), Opal <tmwelton@rogers.com> wrote: >I ...

Line Chart Incorrect Data Display
My data in the worksheet cell is '100%' - on the chart is plotting '118%'. Please help. Thanks! Hi, Not a lot of information to go, but try checking the line chart is not of the stacked variety. Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Gingerly" <Gingerly@discussions.microsoft.com> wrote in message news:622B754D-1EB7-4DD1-8442-9628B3DD8C54@microsoft.com... > My data in the worksheet cell is '100%' - on the chart is plotting '118%'. > Please help. Thanks! Hi Andy Thanks so much for your quick r...

Data Recovery
Hi, I have a dead HD but need to retrieve files. Does anyone recommend a reputable Data Recovery company? Thank you. Do you feel unsure about the general procedure I outlined above? If you are in any way unsure I would take the path you are taking -- ask for recommendations. "kpdeg" <kpdegrave@alphacomm.net> wrote in message news:1158162972.702708.272300@i3g2000cwc.googlegroups.com... > Hi, > > I have a dead HD but need to retrieve files. Does anyone recommend a > reputable Data Recovery company? > > Thank you. > kpdeg <kpdegrave@alphacomm....

update data in table through form
i have created customer data in table and im updating data through form, in the form at one field i have created a formula [bill amt]-[paid amt] and it works but is not updating in the table, kindly help in this i also want to run query in form like i want to see reports from from date to end date of due amt -- srinivas On Fri, 5 Mar 2010 04:09:01 -0800, Srinivas <Srinivas@discussions.microsoft.com> wrote: Formulas like that don't belong in a table, but in a query. So if you ever need this value simply create a query, select your table, and set one of the field...

Outlook Business Contact Manager anniversaries showing up in "OutLook Today" #2
Hi, I just imported about a 1000 customer records into a Business Contact Manager database so I could start sending emails to my clients (as well as perform other tasks). I was appalled to see all the anniversary dates being displayed in my Outlook Today page. Is there anyway to prevent BCE birthday and anniversary dates from being displayed in Outlook Today? I only want my normal contact info to appear here. Thanks! Emile ...

Automatically shift chart data range
I have a spreadsheet full of charts based on monthly data. The charts are intended to show a year's worth of information, i.e., April 2009 to March 2010. There are a series of cells in the spreadsheet with the chart that are autoupdated with new information that the charts are based on. Since there are so many of these charts to update each month, I'd like to know if there is a way to have them automatically shift their date ranges for each new month. For example, Chart A shows data from April 2009 to March 2010. Now that April is here, the chart needs to be shifted to show ...

Inverting a data column
Greetings, I am attempting to invert a column of numeric data (4000-entries) without choosing ascending or descending order as the data has no numeric order. Is there a command or serise of commands to use without writing a formula to erform the action? Thanks, Mike -- Desmo ------------------------------------------------------------------------ Desmo's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=25726 View this thread: http://www.excelforum.com/showthread.php?threadid=391357 There may be neater solutions, but until something better comes along, yo...

Graphing only some of the data?
I have a spreadsheet with groups of repeating rows and cells that look something like this 1AA 1AB 1A 1BA 2BB 2B 1CA 1CB 1C 2AA 2AB 2A 2BA 2BB 2B 2CA 2CB 2C The rows/cells beginning with "1" make up one batch of data. The rows/cells beginning with "2" make up another. I add a new batch every week. I want to create a graph that captures only 1CC, 2CC, 3CC, 4CC, etc. There are always the same numbers of rows between the cells. I've thought about capturing 1CC, 2CC, 3CC, etc in a list in another area of the spreadsheet and graphing that list, but I don't ...

DISTINCT changes data to eliminate duplicates?
This is odd: I have an ODBC connection to an Oracle db. In this one table I see that almost every single record is duplicated resulting in nearly half a million rows. I thought I'd run a SELECT DISTINCT query against it so I could work with a smaller pool of records. No dice. I still see two records but two of the fields have "magically" changed data! There are 33 fields but I'll just demo with the first 4 as all the fields contain duplicate data (both before and after DISTINCT). The first 4 fields are: ID, ADR_TYP_ID, EFF_BGN_DT and ADR_TYP_CD. Before DISTINCT these ...

Look up data in colum a and find match in colum b
I have a sheet containing two lists of ID's e.g. Colum A Colum B abc123456 hjt456897 hjk8966536 jk456654558 hgg009664 jjk5566377 The colums contain 2000 entries. I want to check if the items in colum b match the items in colum a. I would like excel to do this check and in colum c show Match or No Match e.g. abc123456 hjt456897 Match hjk8966536 hgg009664 Match hgg009664 jjk5566377 No match hjt456897 jjk hjkjhd No match Please help. Check your other post for suggestions. Are you having trouble with your newsreader, and not seeing the numerous p...

Data into an Excel Form
I created a form in Excel 2 fields are intended to capture Employee ID# and the other Employee Name (no need for separate fields for First & Last). I have a list of names & ID# also in Excel which I need to create 200+ individualized forms. I would like to generate the forms without having to type the Names & ID# into each field. Is there some sort of merge feature I can use that will save me time? Any solutions will be greatly appreciated. Depending on what this is for, but if it is for print then I'd make the form in Word and merge using the Excel list as data s...

Pulling from a data source and displaying as hyperlink
I have a table with a hyperlink. When I display the data by pulling from a data source - I want the hyperlink comming from the database to actually show up as a clickable hyperlink so I can pass it on with parameters to reporting services. When it displays it, it has the word "Hyperlink:\www..." instead of converting it to an actual hyperlink. I'm guessing there is something similar to do as you would when pulling HTML from a data source where you tell it to not actually display the HTML but convert it... Hi Joe: Sometimes hyperlinks drawn from data sources are not ...

If data present, then display graph.
Is it possible for one to display graphs based on whether data is present in particular cells? For instance, I wish to display the information of cells A1 to A8. A9 is blank. I create a graph to reflect the information of cells A1 to A8. I now fill in information in cell A9. I now wish to display the information of cells A1 to A9. Is it possible for me to create a formula for the graph to automatically represent data from cell A9 when data is present and automatically exclude it when data is not present? Hi, You can create a dynamic graph based on defined names. http:/...

HR Sample Data
I could not find the Sample HR Data in Sample company (TWO) Fabrikam. After Adding Modules (installing HR) I Re-added Sample Data company again. But in vain! Can somebody point me out how to install Sample Data for HR in Fabrikam. Thanks. I'm looking for the same thing! I'm beginning to wonder if there actually is any HR sample data on Dynamics 9.0? Rich "Akram" wrote: > I could not find the Sample HR Data in Sample company (TWO) Fabrikam. > > After Adding Modules (installing HR) > > I Re-added Sample Data company again. But in vain! > > Can ...

"the data at the root level is invalid" "line 1, position 1."
Hi, i have a problem validating xml against schema. I used http://apps.gotdotnet.com/xmltools/xsdvalidator/Default.aspx validator and it says it is fine. Can you tell me why this doesn't work? Thanks! Schema: <?xml version="1.0"?> <xs:schema id="ReportInfo" targetNamespace="http://tempuri.org/Reports.xsd" xmlns:mstns="http://tempuri.org/Reports.xsd" xmlns="http://tempuri.org/Reports.xsd" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="...

saving data from a formula into a variable
hi all, does anyone know how to save data coming form formulaarray directly to a variable? How about you getting your formula to work in the worksheet? Then copy|paste that working formula into your reply. It'll be easier to modify that than to start from scratch. yaniv.dg@gmail.com wrote: > > hi all, > does anyone know how to save data coming form formulaarray directly to > a variable? -- Dave Peterson haven't we been here before? -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) <yaniv.dg@gmail.com> wrote in mess...

data labels in xy scatter #2
John Mansfield I've read your post about I'm interested in this matter and I tested your sugestion but it gives Sintax Error on this instruction: Set RngLabels = Application.InputBox(prompt:="Select the label range:", Type:=8) Can it be because I'm workin with Portuguese version of MS EXcel 2003? What can I do? -- Arcindo RA Lucas -- Arcindo RA Lucas ...

seeking your questions on RMS items--today!
Hi all, I’m the editor of the Microsoft Business Solutions Community site (www.microsoft.com/BusinessSolutions/community). I wanted to let you know that we’re still taking questions on RMS items for our upcoming Q&A with Jimmy Wong, a Microsoft Software Design Engineer in Test (SDET) who works on the Retail Management System team. It doesn't matter how complex or simple your question/concern. Any topic goes, be it discounts, find, inventory, item types, item lookup, pricing, substitutes, or commission. Jimmy's open to any and all questions. :) So if you’ve got a question ...

Flattening XML data during deserialization
Hi, If I have an XML file that stores its data in a hierarchical relationship, is there a way to automatically "flatten" this relationship when I deserialize it (using the XmlSerializer) to a collection of objects? For example, assume that I have an XML file that contains the following data for lastnames/firstnames: Anderson Bob Ann Jones Betty Fred Mark Smith Paul Gina I'd like to deserialize this file into a set of "Person" objects, with each Person containing both a lastname and firstname (rather than separate "LastName" and ...

Sharing Data between Password Protected Spreadsheets
I have two documents. Document A pulls data from document B. Both must be password protected and have the same password. When I open document A, enter its password, and tell it to update from document B I then have to enter the password from document B. Is there any way to make entering the password for document A satisfy the need to enter the password for document B? ...

Funny yellow box when i paste data
I use excel to paste option tables from etrade so I can edit them. I never had a problem with this, but recently when i paste my data a little yellow box with what appears to be a scroll icon inside of it appears in the upper left corner of certain cells. I've noticed a drastic decrease in preformance since this has start. One of my sheets is completly unworkable in fact. What is this box and how can I keep it from being displayed? Thanks for any help. Excel has built in error checking, which is of very limited usefulness. You can turn it off from the Options dialog (fr...

SUMMARIZING DATA BASED ON DATES GROUPED IN WEEKS
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C6ED3C.00C7C2B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello! 1) The RAW DATA SHEET is where I maintain a cash receipt register in a = list form on a daily basis. Any date may have multiple entries due to = different customer payment. 2) The SUMMARY SHEET shows how I want to summarize the data contained in = RAW DATA SHEET. Basically the summary sheet is summing based on the date = grouped in weeks. =20 RAW DATA SHEET DATE DAY CUSTOMER AMOUNT=20 ...

Can you manually delete data from the Account and Contact tables?
I am doing some testing and am presently deleting data from the Accountbase and Contactbase tables in the MSCRM database. I know that the supported method is to backup and restore the databases but ... Anyone have experience with this? I am only loading data from the CDF database to these 2 tables. In order for me to delete the data I have to temporarily disable "Enforce Integrity" in the 4 relationship between Accountbase and Contactbase. It works ... but will I have problems in the future after I have finally loaded all my good data? Danny Danny, There could be problems introd...

Presenting all months in a year in a Pivot Table/Chart when all months are not included in source data
Excel 2007, Windows 7 I'm creating a series of pivot tables and charts to show price data by month in a given year for different categories of products (in different locations). If I have entries for most but not all months in a given year, is it possible to have excel render a table and chart that displays all 12 months of the calendar year even though not all are included in the source data? As it works by default, it only presents data for those months that are included in the source data. I know a work around would be to include the missing months with a blank entry in the source d...

Advanced Lookups with a secondard sort
Hi, When I put in an Advanced Lookup for a Segment (Entity Code - 0WHIIN), it looks like the account numbers are sorting by Entity and then DEX ROW ID and not by account number. The sort should be by Entity (Segment 2 - Entity and then by Main Account Number - 00010, not Dex Row ID. This is a sample of the sort This is the Dex row ID for the accounts 00010-0WHIIN-00-00 1 00200-0WHIIN-00-00 2 00250-0WHIIN-00-00 3 00260-0WHIIN-00-00 ...