Lookup with constraints

grev

New Member
Joined
Sep 4, 2002
Messages
21
I have a table with 5 possible entries. The look up table with possible values has 150 entries. In other words I am trying to establish stements where if the date matches any of the 150 entries it pulls out the 5 (will always be 5) values and their corresponding column values. I am having trouble writing the statement in the 5 cells. How can I incorporate the entire table but get 5 different values.

Can you help? Sorry for the mumbo jumbo.
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
I'm not following why you need to include all 5 values. Could you post a sample of the 5 entries to the board? If you begin you table with <pre> and end it with </pre> your spacing will be preserved and it will be easier to read.
 

grev

New Member
Joined
Sep 4, 2002
Messages
21
Here goes
Table 1 (The result Table) (sheet 1)
<pre> Date 9/11/02
Prt # Model Qty
x 1204 30
y 1305 40
z 5005 100
a 1005 100
b 2005 20
</pre>

The look up table (reference table)(sheet 2) has 150 of these values sorted by part# and date. From that table I want to pull only the part#'s and its respective model and qty that have the correct corresponding date for sheet 1. Basically it is two seperate worksheets.

Hope this helps.
 

Jim North

MrExcel MVP
Joined
Jun 20, 2002
Messages
791
On 2002-09-13 08:32, grev wrote:
Here goes
Table 1 (The result Table) (sheet 1)<pre>
Date 9/11/02
Prt # Model Qty
x 1204 30
y 1305 40
z 5005 100
a 1005 100
b 2005 20</pre>

The look up table (reference table)(sheet 2) has 150 of these values sorted by part# and date. From that table I want to pull only the part#'s and its respective model and qty that have the correct corresponding date for sheet 1. Basically it is two seperate worksheets.

Hope this helps.

So you are trying to calculate the Qty field??

How is your Model field used, or do you only care about Prt#??

_________________
JRN

Excel 2000; Windows 2000
This message was edited by Jim North on 2002-09-13 08:44
 

lenze

Legend
Joined
Feb 18, 2002
Messages
13,690

ADVERTISEMENT

I thinking you are trying to pull the information from your main table for all items by Date. If so, you can use Advanced Filter. In the example, the yellow represents your main table. The Green is the criteria range, and the blue is the output range. I recorded a macro (FilterDate) to pull data from the main table to the output range. If you change the date in the criteria range, you can rerun the macro to display info for that date.
Book1
ABCDEFGHI
1DatePrt #ModelQtyDate
29/11/02x1204309/12/02
39/11/02y130540
49/11/02z5005100DatePrt #ModelQty
59/11/02a10051009/12/02x120440
69/11/02b2005209/12/02y130560
79/12/02x1204409/12/02z500590
89/12/02y1305609/12/02a1005110
99/12/02z5005909/12/02b200530
109/12/02a1005110
119/12/02b200530
129/13/02x120440
139/13/02y130565
149/13/02z500590
159/13/02a1005100
169/13/02b200535
...


Here is the macro code:
Sub FilterDate()
'
' FilterDate Macro
' Macro recorded 9/13/2002 by lenze
'
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F1:G2"), CopyToRange:=Range("F4"), Unique:=False
End Sub

Note: You can also use MSQuery for this.

HTH
 

grev

New Member
Joined
Sep 4, 2002
Messages
21
I think this is going to work. Will let you know. Thanks for the help.
 

grev

New Member
Joined
Sep 4, 2002
Messages
21

ADVERTISEMENT

I am somewhat confused with the code. Is F4 assigned to the macro. I have done Macro's before but your code seems to be above my level. Can you explain it a little more? Do I edit this in the Edit function in creating the Macro? Thanks for the help.
 

grev

New Member
Joined
Sep 4, 2002
Messages
21
Is there an easier way to do this? I am still having trouble with the code. Need help.
 

s-o-s

Active Member
Joined
Apr 14, 2002
Messages
384
If your not happy with Macro's then you could try 1 of the following:
1) Doing it manually.

Select your whole table.
Insert an autofilter.
In the Date column select the date you are interested in.

2) Pivot Table.

Select your whole table.
Data | Pivot Table.
Next.
Next.
Layout.
Drag Date to Page
Drag Prt # to Row
Drag Model to Row
Drag Qty to Data

Double Click on Prt # field change Subtotals to None. (This stops doubling up the lines)

Click OK

You can now change the Top (Page) drop down to the date of your choice and this will display the information you require.

3) Regarding the macro by Lenze.

Sub FilterDate()
'
' FilterDate Macro
' Macro recorded 9/13/2002 by lenze
'
Range("A1:D16").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"F1:G2"), CopyToRange:=Range("F4"), Unique:=False
End Sub
assuming your data is not in the format above.

Change the A1:D16 to include your entire Data table.

If you have a Target Date in cell Z2 make sure that the word Date appears in cell Z1 then change F1:G2 to Z1:Z2

Lastly if you want the output to appear in a table beginning in Z10.

Change the F4 to Z10.


_________________
Hope This Helps.
Sean.<A HREF= "http://website.lineone.net/~s-o-s/Index.html">

This message was edited by s-o-s on 2002-09-15 15:29
 

Watch MrExcel Video

Forum statistics

Threads
1,118,286
Messages
5,571,313
Members
412,382
Latest member
Langtn02
Top