LOOKUP Help #7

Hi,

I have a column (e.g. Column X) that lists a series of dates, entered
by the user.

I have a table array (Columns A-L) of all the months, Jan-Dec.

What I want to be able to do is, under each month in the table, look
into Column X and list all the dates within Column X that are relevant
to that month.

I've tried LOOKUP but because dates are formatted like 31259 I dont
know how to write the logic.

Can anyone help?

Thanks a lot

0
dv (2)
5/14/2007 11:16:26 AM
excel 39879 articles. 2 followers. Follow

4 Replies
265 Views

Similar Articles

[PageSpeed] 55

One way using non-array formulas is illustrated in this sample construct:
http://www.savefile.com/files/720924
Placing source dates under correct month col.xls

Source dates (ie real dates) assumed input in X2 down
A1:L1 contains the text: Jan, Feb, ... Dec

In Y2:
=TEXT(X2,"mmm")
Copy down

In Z1: =A1
Copy Z1 to AK1

In Z2:
=IF($X2="","",IF($Y2=Z$1,ROW(),""))
Copy Z2 to AK2, fill down to cover the max expected extent of data in col X

In A2:
=IF(ROW(A1)>COUNT(Z:Z),"",INDEX($X:$X,MATCH(SMALL(Z:Z,ROW(A1)),Z:Z,0)))
Copy A2 across to L2, fill down to cover the max expected extent of dates 
for any one month. This returns the source dates from col X nicely under the 
correct month's col, with all results neatly bunched at the top.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"diver_sol" <dv@sollich.co.uk> wrote in message 
news:1179141386.917206.138320@l77g2000hsb.googlegroups.com...
> Hi,
>
> I have a column (e.g. Column X) that lists a series of dates, entered
> by the user.
>
> I have a table array (Columns A-L) of all the months, Jan-Dec.
>
> What I want to be able to do is, under each month in the table, look
> into Column X and list all the dates within Column X that are relevant
> to that month.
>
> I've tried LOOKUP but because dates are formatted like 31259 I dont
> know how to write the logic.
>
> Can anyone help?
>
> Thanks a lot
> 


0
demechanik (4694)
5/14/2007 2:42:55 PM
> In Z2:
> =IF($X2="","",IF($Y2=Z$1,ROW(),""))

And if you want the dates under the month cols to be sorted in 
chronologic/ascending order, just do a slight tweak to the formula in Z2, 
viz. use instead in Z2:
=IF($X2="","",IF($Y2=Z$1,$X2+ROW(),""))
Copy Z2 to AK2, fill down to cover the max expected extent of data in col X
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---  


0
demechanik (4694)
5/15/2007 2:34:07 AM
Wow, thanks a lot for that, a lot more than i was expecting!!

Thank you very much, I hope it didnt take you too long

0
dv (2)
5/16/2007 7:42:24 AM
You're welcome. Glad to hear that.
-- 
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
--- 
"diver_sol" <dv@sollich.co.uk> wrote in message 
news:1179301344.095407.67430@q75g2000hsh.googlegroups.com...
> Wow, thanks a lot for that, a lot more than i was expecting!!
> Thank you very much, I hope it didnt take you too long
> 


0
demechanik (4694)
5/16/2007 2:41:19 PM
Reply:

Similar Artilces:

Outlook 98
I have someone who have multiple appointments in his calendar. He wants to show 7 days in the outlook today folder but it only shows 5. There are appointments on the days that are not showing. I have set under TOOLS, OPTIONS to show all 7 days of the week but it still only shows 5 days. Is there is a limit as to how many appointments are to be displayed? ...

Money 2003: help - how can I add trended items back into the cashflow analysis
I deleted some trended items from my cashflow analysis by accident on mny 2003, Is there any way of getting them back in there??? thanks in advance. Click on the 'customise cash flow' option which is either on the left hand side of the cash flow display, or right clicking on the chart. To put it back, you need to select an option in there. -- Glyn Simpson, Microsoft MVP - Money Check http://support.microsoft.com/default.aspx?scid=fh;EN-GB;mny for UK tips and fixes for MS Money. To send Microsoft your wishes or suggestions, use http://register.microsoft.com/mswish/suggestion.asp or...

help #26
Can anyone out there help me with setting up a graph for the following info. See bottom of page for example of what i am looking Cost Centre Received in Finance No Days 2010 May-04 29 2010 May-04 15 2010 May-04 21 2001 May-04 14 2026 June-04 34 2038 June-04 30 2010 May-04 17 2028 June-04 39 2010 Ma...

Help for formula.
Hi. I want to solve a linear of n X n equations system in Excel, where the solution can be derived as follow: Xn = ao Xn-1 = a1 * Xn Xn-2 = a2 * Xn-1 + a3 * Xn Xn-3 = a4* Xn-2 + a5 * Xn-1 + a6 * Xn e.g. The an coefficients (a0,a1,a2,….an) are all known. Lets simplify the above system using an example. Lets say that n=5 and: X5=7 X4=2*X5 X3=3*X4+2*X5 X2=1*X3+3*X4+4*X5 X1=1*x2+1*x3+1*x4+2*x5 (Then the solution is: X1=210, X2=126, X3=56, X4=14 and X5=7) Any idea how can I solve these n x n equations in general form in Excel? (I tried with sumproduct() but I stuck in coeff...

Need a help with e-store script
Could you please help me? (sorry if this is a bit off topic) I am not technically minded, but I would like to start an online store. What should I start with? Are there any pitfalls that I should be aware of? Thank you ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Try this - http://www.ecommerce.com/?pg=products. very good impression! ----------------------------------...

help with "=(A3*A3)" command #2
Okay, what i was doing was pasting the values from a webpage. So opened up a new worksheet and hand typed the value into A3 and th formula now works -- happe ----------------------------------------------------------------------- happee's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1536 View this thread: http://www.excelforum.com/showthread.php?threadid=26987 ...

IF FORMULA need help
Please, I am trying really hard to work this out, but still I couldn't come to result I want. This is the example (invoices - paid, not paid...) column A..................... column B...................... column C DUE DATE....................PAID ON........................COMMENT 10.11.05.....................07.11.05........................paid, on time 15.11.05.....................20.11.05........................paid, late 20.11.05...................(empty cell)......................not paid, late 04.12.05...................(empty cell)......................not paid, not late So, you...

HELP Please with Offline Address Lists
Using OL2003 with Exchange 2003.... I have created several address lists in Exchange SM under "All Contacts". I've added all of those to the list of address lists found in the Properties of the Default Offline Address List. However, using OL2003 offline it appears that I can only have 1 of them?!?!? When offline, I can see the Global Address List, but when I switch to another address book it gives a "no network" error. When back online in, in OL I go to Tools-->Send/Receive-->Download Address Book. It asks which one. If I switch to something other than the...

Outlook Mail pre-view window bigger..Calendar disappeared..Help
I'm new to Microsoft outlook and like it very much! On my outlook mail home screen I had four columns.. column 3 had a mail preview window and column 4 had a small one-month calendar. I thought I was hitting a right arrow on the calendar to change my calendar from August to September but instead my calendar disappeared and my mail preview window got bigger to use the space vacated by my calendar. Yikes! How do I get the calendar back? Outlook 2007? The right pane is called the To-Do Bar. To make it bigger again simply press the "<<" sign at the top of it. Other wa...

problem with NdisReturnPackets ( ) please help me!
hello all: get BSOD on xp sp2. this code: //////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////// NDIS_STATUS PtReceive( IN NDIS_HANDLE ProtocolBindingContext, IN NDIS_HANDLE MacReceiveContext, IN PVOID HeaderBuffer, IN UINT HeaderBufferSize, IN PVOID LookAheadBuffer, IN UINT LookAheadBufferSize, IN UINT PacketSize ) { PADAPT pAdapt = (PADAPT)ProtocolBindi...

Date conversion, PLEASE HELP
I am importing data from an OBDC datasource. One of the fields is a date field that imports in this format "20040818", as a number. I am trying to convert this value to a date format and have had no luck. If anyone can provide assistance, I am grateful... MyDate = DateSerial(Left([ImportedDate], 4), Mid([ImportedDate], 5, 2), Right([ImportedDate], 2)) -- Ken Snell <MS ACCESS MVP> "shawn" <shawn.abbott@mrgasket.com> wrote in message news:8ac901c4854e$3aa483e0$a601280a@phx.gbl... > I am importing data from an OBDC datasource. One of the &g...

How do I show only certain values from a lookup?
I've created a new form, used for ordering goods at our line of suppliers. In the form, I use a lookup field for selecting the customer. However, to be able to find our suppliers faster, I want to see only customers whose customertypecode is set to "supplier". Could someone please tell me, if this kind of conditional lookup is possible? Hi Thomas, You might want to read this blog: http://ronaldlemmen.blogspot.com/2006/12/filter-data-in-crm-lookup-field.html It explains how to filter the lookup results. Good luck, Bertil "Thomas H. Bech" wrote: > I've cr...

PP2003 every image and embedded object turned into red cross, help
I have been working of a PP presentation (in Office 2003) for a number of days. Today when I opened the document all of the images and embedded excel worksheets have turned into boxes with red crosses on them. If I double click on the red crosses that I know are the embedded worksheets then the worksheet opens in Excel with all my data displayed correctly. This shows that the correct data is in the file. However when I click outside the worksheet (i.e. back on the slide) the worksheet is shown in huge scale (i.e. just a few cells is as big as the slide). I then reduce the size o...

Indenting causes my text to disappear.. help!
Hi, Ok i've wrote a project that needed to be completed on 2 columns per page. Which i have done no problem. however, they told me now that i need to re format the columns so that there is 0.6cm spacing between the columns, and a left indent of -1cm and a right indent of -1cm. When i highligh all my text and do the indenting and the spacing it means that some of my text disappears.. if i go through each line on the left column, and press enter it reappears on the next line, however i dont know how to access my hidden words on the right hand column? PLEASE HELP. Is there...

Help with excel 2007 chart
Hey, this seems fairly simple but is stumping me. I have a table with static data that shows my budgetary costs per month for the last 2 years. The row labels in my table are the categories (mortgage, insurance, power, gas, phone, etc.) and the column labels are each month for 2007 and 2008. What I want to do is create a chart that will graph the data (line chart) for each category over the last two years. I would like to choose what category to display on the chart so they're not all on the chart at the same time. I have tried a Pivot Chart, but it's just not displaying the data corre...

Help with drop down boxes/menus
Can some assist me with adding a drop down menu to a cell ----------------------------------------------- ~~ Message posted from http://www.ExcelTip.com ~~View and post usenet messages directly from http://www.ExcelForum.com ~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements Look at VALIDATION under the DATA pull down menu Good Luck Mark Graesse mark_graesser@yahoo.com ntrain22, It's all detailed at this site http://www.contextures.com/xlDataVal01.html Dan E "ntrain22" <ntrain22.xrj4p@excelforum-nospam.com> wrote in me...

Access 2003 query help please
Hello, I am in need of some advice for a table create query. I have to work with a database that is imported using XML from an online database. Unfortunately the online database is a single (flat) table of membership data. I need to generate a mailing list from that data. The problem is that each record may contain one of two addresses for the mailing to a member. One is the organization address they work at the other is an optional mailing address. If the optional mailing address is present those fields must be used, otherwise the fields containing the organization address should be us...

SRS Data connector remote instance setup not working HELP urgent
Hi guys, Ive been on this for a week now its really annoying. If I do setup of srs just by double clickign setup file and pointing to named instance setup passes but srs doesnt work, I imagine it tries the default instance SRS. If I use the XML file to configure it, it doesnt pass the reporting service test, even though , I paste the url from the XML in a browser and see all the crm reporting db entries. Heres a paste of my XML file.. Please help! crmsetup> <srsdataconnector> <configdbserver>com-sharepointa\crmsql</configdbserver> <autoupdateconfigdb>...

FRx 6.7: Accounts Not Displaying In Row File
I have successfully created Financial Statements for two of our three companies. However, I’m having a problem with the third one. In creating the row file, I specify a range from account 30000 through 99999. Most accounts show up in the list. Some don’t. The following account string will be my example of an existing account, with a balance, that does not show up in the row file: 00000-30035-00000 (30035 is the main account) If I manually insert a row into the row file, type in the description, and click the down arrow for the GL Link, the account does not display in the drop down. Ho...

Help #10
Hello I was wondering if anyone could help me. I have made a receiving log for different suppliers. The first two lines is the same in all the sheets. What I have done is make one workbook with 30 different sheets each one being a supplier and the first one being a master sheet. Is there any way to make it so if I change anything on the master sheet it will change on the other sheets. The only thing is that I don't want all the information to change just the two first lines. Thank you and have a great day Jessica ------------------------------------------------ ~~ Message posted fro...

Setup Error HELP ME Please
Dear All, I get error message while installing CRM Server 1.2 beta in our domain. All the pre-requisites seems to be installed properly (Active Directory with SP4, DNS with rev.lookup zone, Exchange 2000 with SP3, IIS, Indexing service, MSMQ, XML, MDAC, .Net framework and SQL Server 2000 with SP3). The installation is creating an OU named Adventure Works Cycle The database on SQL Server named Adventure_Works_Cycle_CRMCRYSTAL Adventure_Works_Cycle_METABASE Adventure_Works_Cycle_MSCRM. Setup is also able to create Default web page in IIS. There are no special characters or ampersand or under...

Building plug-ins to MS Outlook
Hello all, my company has almost completed development of a plug-in to MS Outlook that enables the sending and receiving of SMS text messages, the product is feature rich and equal to anything else on the market. Mediaburst are experiencing problems with debugging the software and are still experiencing occasional 'crashing'and 'hanging'of Outlook, I need to identify a comapny who can assist us. I am not sure if this is the correct forum to ask, if not perhaps someone would be kind enough to redirect me to a more suitable place? Please email me should anyone know of a...

help with outlook #4
Hi to all, I am new to using outlook and need some help from u guys. I am giving u the details I want.I am using outlook 6.0 Kindly Help me in this regard 1. When ever I open the outlook it is open to all to look into my inbox is there any way to protect it by giving a password so that only I would be able to view the inbox and other mails I got. 2. Regarding newsgroups is there any way I can retrieve all the messages? And if not is there any way I can search all the messages in that group for a particular query? Right now I can search only in the messages which I have downloaded. 3. How to pu...

Still looking for help!
I have been working on this for a long time. It is not a router issue and it seems that all the permissions are set correctly. I have searched long and hard for the answer and have not come up with any. I can hit the webmail from inside but not outside. When trying to connect it seems as though it is trying to use the credentials from the machine I try to use. Since many of these machines are not on the same domain and some dont use network passwords at all OWA will not allow the connection. If anyone one has any suggestions i would really appreciate it. "trapped" wrote: >...

The printer won't print the whole tear-off area. Help!
I've adjusted the layout guides but I can find out how to adjust the printing area. Thanks, Nora Could be the limitations of your printer margins. -- Mary Sauer MS MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Nora" <Nora@discussions.microsoft.com> wrote in message news:F46E78F7-56EC-4D73-BA14-D0F9A43A056A@microsoft.com... > I've adjusted the layout guides but I can find out how to adjust the printing > area. > Thanks, Nora "Mary Sauer" wrote: > Could be the limitations of your printer margins. &...