index & match month

I would like a formula that would match any month I want. IF statements have 
a max of 7 criteria and I need more than that.

I would like to enter a month in another spreadsheet and return the data for 
that month from the data worksheet.

 Type 	 Jan 	 Feb 	 Mar 	 Apr 	 May 	 Jun 	 Jul 	 Aug 	 Sep 	 Oct 
 LR 	 87.55 	 126.77 	 111.63 	 174.66 	 132.49 	 117.70 	 135.82 	 203.85 	 
184.11 	 177.43 
 PC 	 3.91 			 1.90 	 6.63 	 15.33 		 23.54 	 126.09 	 116.21 
 RE 	 98.23 	 29.75 		 17.27 	 5.52 			 10.25 	 -   	
 SP 		 92.93 							 -   	

0
Utf
12/2/2009 6:49:01 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
1088 Views

Similar Articles

[PageSpeed] 22

Lets say you input type into cell Z1, month into cell Z2 (using same format 
as headers). Formula then becomes something like:

=INDEX(B2:J10,MATCH(Z1,A2:A10,0),MATCH(Z2,B1:J1,0))
-- 
Best Regards,

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


"Charp" wrote:

> I would like a formula that would match any month I want. IF statements have 
> a max of 7 criteria and I need more than that.
> 
> I would like to enter a month in another spreadsheet and return the data for 
> that month from the data worksheet.
> 
>  Type 	 Jan 	 Feb 	 Mar 	 Apr 	 May 	 Jun 	 Jul 	 Aug 	 Sep 	 Oct 
>  LR 	 87.55 	 126.77 	 111.63 	 174.66 	 132.49 	 117.70 	 135.82 	 203.85 	 
> 184.11 	 177.43 
>  PC 	 3.91 			 1.90 	 6.63 	 15.33 		 23.54 	 126.09 	 116.21 
>  RE 	 98.23 	 29.75 		 17.27 	 5.52 			 10.25 	 -   	
>  SP 		 92.93 							 -   	
> 
0
Utf
12/2/2009 7:10:01 PM
Reply:

Similar Artilces:

Change the field for indexing
Hi, We would like to change the way the index (alphabet at the bottom of the display grid) works. Currently in the Opportunity Entity, the index is on "topic" and we would like to change that to index on "potential Customer". We always use an Account in this field so there is no issue with this being a contact. Thanks! Patti ...

How to implement a query that return data with no match
Hi, i have read some posts of inner join and outer join, but no one could help me to solve my problem :( I have 2 tables 1) tblRecords (having, article; User;date in; date out; service) 2) tblUsers (user) what i need is to select from tblRecords those records that does not match the users in the tblUsers, so if for example i have 20 records in the table 1 (all from diferent users) and in table 2 i have 3 users, the result will be like if i delete from tblRecords the records that match tue users in tblUsers. But i don't want to delete those records... so how can i make a select query to ...

Calculating the days in a month
I have a Control which is a Date field named 'NEWTOPD'. Based on the date entered in the field it calculates the days offor the whole month making amends for Leap-year as well. If I want only the number of days in that month starting from the date entered in 'NEWTOPD' - to be entered in a calculated text field (which is a number field) named 'Text361' how should I modify the following code? Text361 stores the data in (PDDAYS). 'UPDATE (PDDAYS) BASED ON NEWTOPD DATE Dim iMon As Integer Dim sDate As String ...

Convert Date in Text to month and Year
I am trying to convert text to month and year. In the cell it displays as 07/09 i want it show Jul-2009 or 2009-Jul. When i use DATEVALUE or DATE it adds current year and displays something like 02/15/2010. Any idea what i am doing wrong.. thanks hi, ! try to follow (and provide feed-back in) your previous post (???) regards, hector. See your previous posts. Regards, Fred "Excellency" <Excellency@discussions.microsoft.com> wrote in message news:BBD51CC5-AC5A-4513-851B-B329054527E3@microsoft.com... >I am trying to convert text to month an...

Continue page numbering from previous document while Indexing
Hi! I have a very long document that I have split into several smaller documents to work on and consequently, I have set the page numbering for each subsequent according to the last page of the previous document: "Document #1: Page 1-500" "Document #2: Page 501-750" "Document #3: Page 751-900" However, when I do Indexing in Document #2, regardless of the page number I have set up, it reverts to Page 1 when updating the Index and resets the entire page numbering in that document. How can I 'force' the Indexing to recognize the subsequent pag...

Transaction matching on originating currency
Most of our customers have contacted us with a request to do transaction matching in originating currencies in the General Ledger Transactions Link Maintenance window. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community...

Why Re:s don't match original posts
Hi, does anybody know why large number of Re:s doesn't match original posts? I use Outlook Express as a news reader and those Re:s drive me crazy!!! Vlado What news server are you using? If you are using your own ISP's server, try: news.microsoft.com Specify a new account on this server under: Tools | Account IME, most responses are correctly threaded on that server, though you still get occasional posts that disappear into thin air. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

SQL Ethnic Name Matching
I have a table of surnames and ethnicity. I want to choose one ethnicity and the number of characters going backwards from the end that should be matched to approximate ethnicity. This will be used to extract names of one ethnicity form another table. I'll be using MS Access initially, so I assume their character matching routines (those in the help file look like the ones in GW Basic) will do. (Dunno if they exist on other SQL.) Might someone suggest the SQL code for this op? Are LIKE and SIMILAR universal in SQL? I have a mismash of experiences with broad gaps. I used IBM DB2 in...

EXCEL VLOOKUP or MATCH fx Problem
I am attempting to create a simple excel form where the user can choos a house name and the square footage will appear for each plan. Fo example, when you choose house1, 100 appears in the next cell, when yo choose house 2, 200 appears, etc. I have created a form using th formul =CHOOSE(MATCH(A1,{"house1","house2","house3","house4"},100,200,300,400) The problem is EXCEL is not recognizing the values over house3. When select house 4 from my validation menu, it will have either an erro message or a different value. Any help will be much appreciated ...

Match / Sum Values
In column B i have a list of area names ( cp, ss, dr, sp ) and in column J and L have values in the row for these areas. I want to have a formula to say ( count all the cp, ss, dr, and sp) and sum the values in column J to match each area and sum values in column L to match each area. I though of doing vlookup for each area then suming the vlookup. Is there and better way? Having a formula for each area would be fine because i have to show the totals for each area anyway. Hi =SUMPRODUCT(--($B$2:$B$100="cp"),$J$2:$J$100)+SUMPRODUCT(--($B$2:$B$100="cp" ),$L$2:$L$100) ...

Creating month sequences
I was wondering... Is there an easy way to create a range of cells that a) show just the month and year and b) can calculate the next month and year for each cell in the range? Example: jan-06 feb-06 ... nov-10 dec-10 Enter the first 2 dates (I use the m/d/y format): A1 = 1/1/2006 B1 = 2/1/2006 Select both A1 and B1 and format the way you want: Format>Cells>Custom mmm-yy OK With both cells still selected "grab" the fill handle and drag across as needed. -- Biff Microsoft Excel MVP "Henry Stock" <henry@henry-stock.com> wrote in mes...

Help with a Monthly Summary Report
Hi, Since this question involves both a query summary and a report, I tried to cross post to microsoft.public.access.queries. I am working of a volunteer reporting form for a charity. They have to submit a monthly summary report to the government as required by their grant funding requirements. We submit a month in arrears. For example, we submit January’s activity report at the beginning of March. By the time we run the report, all of the activity has been recorded. Below is a sample of the report: Program | Volunteer Ct | Total Hours | Total Miles | ...

Days containing Items are not Bold in anything 2 months earlier than current month
Hi fellows, Thanks for reading my first post to this list. I have a simple question that I still cant answer :( I just did an archive of Outlook 2003. After I did that, the days containing Items are not Bold in anything 2 months earlier than current month. Funnily enuf, the days still contain those items. They are just NOT bold. Anything 1 month earlier than the current month is bold so its not an options-configuration problem, I thinik. I just archived folders that had items of last year, thats all, nothing else. I appreciate any help/pointers/tips given. Thank you. This is the defau...

Result of date as Month-2 digit Year
I have columns of dates that the users must enter as actual dates. I can format to display month and two-digit year...but when creating pivot tables it reconizes the entire date. Since I want to summarize only by month and year or by year only, is there a formula or statement that will change the date to month-year or year status only? Thanks. -- Linda Hi Linda Sure. Rightclick the date field header in the Pivot table, choose menu "Group and show detail" > "Group"... . You will get a list offering Seconds, minutes, hours, days, months, quarters, years. Try sel...

how to convert 12/10/1970 to years/months/days
can any one help me to covert 12/10/1970 to years/months/days Hi With date in A1 =Year(A1) =Month(A1) =Day(A1) -- Regards Roger Govier "doctor who golf" <doctorwhogolf@discussions.microsoft.com> wrote in message news:0C009ACE-2631-4770-9CCE-20308E198D58@microsoft.com... > can any one help me to covert 12/10/1970 to years/months/days Maybe you mean =DATEDIF(A1,TODAY(),"Y")&" years, "&DATEDIF(A1,TODAY(),"YM")&" months, "&DATEDIF(A1,TODAY(),"MD")&" days" -- HTH Bob Phillips (replac...

Formatting for 15th and last day of the month
Hello, I need to create a worksheet that reflects exact payroll dates for every month of 2006 and 2007. Payroll is on the 15th and last day of every month. Obviously I can do this manually, but was wondering if there was a way to create the dates automatically. So, for example, create a formula that would automate dates as such, taking into consideration the above-mentioned parameters: 12/15/2005 12/31/2006 1/15/2006 1/31/2006 2/15/2006 2/28/2006 etc. Thank you. Put your starting date in A1, then in A2 add =DATE(YEAR(A1),MONTH(A1)+1,IF(DAY(A1)=15...

publish to web- index file problem
Hi, When I publish my files to the web, I do not get my first page (index.html). Everything else is created perfect except for the first page. I gave names to all of my HTML files but the program doesnt want to give me my first page! Could you please help me with this? In the future when you use the Microsoft Community please take note that there are topic specific forums for a product, designed to enhance the level of support you'll find in the Community. For your posted topic our WebDesign forum - microsoft.public.publisher.webdesign - would be the applicable resource. You failed ...

Using 'Not Like' for exact string matches
Is there a performance difference between these two query criteria statements? Not Like "aa" and Not Like "bb" vs. Not "aa" and Not "bb" I've seen where you should only use Like when a wildcard is being used. I'm looking to speed up these queries any way I can. Thanks in advance. I believe that you will see no difference in performance for the two queries. Have you indexed the field that you are applying the criteria to? Indexing will usually make query performance quicker. "Usually .. quicker" meaning that if you are adding r...

Copy to next empty row, if not a match
I have the following code to copy data from one sheet to another: Sheets("CallerInput").Range("B29:BQ29").Copy _ Sheets("InfoLoader").Range("B" & LastRow(Sheets("InfoLoader")) + 1) However, cell B29 in the range to be copied is a date. How can I amend this code to first look down column B of the paste target in "InfoLoader". It would then overtype any day already held, or otherwise paste to the end? Thanks in advance Steve You can use application.match() to see if there's a date that matches: Option Explicit Sub testme(...

Seek (ADO) finds same absoluteposition for two different indexes
The function below takes a serial number entered in the "Serach" control and then seeks that value in the data table called "PCM Interfaces (Main Table)". If seek does not return EOF then the absolute position is found and then the form is set to show that record. This function is just a form record locator.The problem is that the same AbsolutePosition (1) is found for records 1 and 2, when their respective serial numbers are searched. So the symptoms are that when serial number 1 is searched it locates record 1. When serial number 2 is searched it locates record 1. When se...

run-time error 13: mis-matched types
For some reason we are occasionally getting a "run-time error 13: mis-matched types" come up on either of our two registers. After it occurs, RMS completely shuts down and you have to restart the POS program. I think this is happening on credit card sales, and occurs after the credit card has been sent to PC-Charge (the credit card sale goes through to our processor) and before the transaction is posted into RMS, so even though the card has been charged, the sale is not recorded in RMS at all. To remedy this, we have to credit the customer through PC-Charge and re-ring the o...

Counting Consecutive Months in Top Third
Ok. I need to have a formula which would look at a number of columns and tell me now many 'consecutive' months someone has been in the top 33% of the numbers in that column to date. IE . . .Ann was in the top 33% in Jan and Feb but not in March so in March I would want the formula to spit out '2', In April I would want it to start over again at '1' (If in fact she was in the top 33% in March, if not then it should say '0'). Hardest part in figuring this out (in my head anyway) is how to get it to give me the number as of the current date. Figures =NON() would ha...

Monthly year to date average
I have scores for given records for a particular date: 3/15/07 67 4/1/07 43 1/15/07 56 I would like to be able to average these scores by month, but have a running total: January 56 February 56 March 61.5 (average 56 & 67) April 55.3 (average 56, 67, and 43) I think I need to use a DAvg function, but it isn't working yet. *IF* you have only ONE record per month, you can try: SELECT a.date, LAST(a.qty), AVG(b.qty) FROM tableName As a INNER JOIN tableName As b ON a.date >= b.date GROUP BY a.date that would give: 1/15/2007 56 56 3/15/2007 67 61.5 4...

Showing data per month & year for comparison
Hi, I need to show data for a particular month for 2 years (July 2004 vs. July 2005) and YTD data for 2004 & 2005 on the same pivot table. This will allow comparison between 2 months and YTD sales on the PT. I have grouped the dates and put them along the top of the pivot table and can choose month and year, but the YTD figures always change to the month figures. How do I get the YTD figures to not change when I choose a month? Confusing...yes. -- gman Hi, Can you upload a watered down version of the spreadsheet, because it is difficult to work out what the problem is? I will t...

MATCH, LOOKUP, macro?
I have a list of 176 items and another list of 870 items. I need to see if the items in the smaller list are on the bigger list. There is no guarentee of any ascending or descending sort order. And I need to match two values in the same row. So: IF (A2 is in B2:B871) _ AND **in the same row** C2 matches the value in col D _ THEN return "Yes" MATCH and LOOKUP seem to require a sort order. And I'm not sure how to use the row number with those. I could cobble together a macro, but I thought a built-in function might be faster and easier (not to mention I might actuall...