#### 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.

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
> 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,
=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
> 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

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...

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...

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...

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...

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...