Sort/Filter/Lookup

M

Mike

I have a worksheet that has 4 columns that are entered by the user and 2
columns that I want filled from a 2nd worksheet based on the information
entered into the first 4 columns.

How do I go about doing this?

Mike
 
C

CLR

The two columns you wish fed in from the second worksheet would probably
contain some sort of VLOOKUP formula, the construction of which would be
specific to your application. We would need to know exactly what part of the
user-entered data you wished to be used to call in the new data, and exactly
where the new data is located...........please post back with more specifics
and someone will be glad to help...........

Vaya con Dios,
Chuck, CABGx3
 
M

Mike

Sorry for the lack of info. I was trying to keep the volume of words down.

Sheet1
Column 1: Country - User Entered
Column 2: Style - User Entered
Column 3: Current - User Entered
Column 4: Connector - User Entered
Column 5: Part Number - Pulled from second worksheet
Column 6: Comments - Pulled from second worksheet

Sheet2
Column 1: Part Number
Column 2: Style
Column 3: Current
Column 4: Connector
Column 5: Comments

I want to take columns 2, 3, & 4 of Sheet1 and use that information to pull
from Sheet2 the Part Numer and Comments that go with them.

Hope this provides a better picture of what I'm trying to do.

Mike
 
C

CLR

You need to change Sheet 2 a bit......insert a new column A to the left of
your present PartNumber column, and assuming you're using the header
Partnumber, etc in row 1, then in cell A2 put this formula and copy down.....
=B2&C2&D2

Then in cell E2 of Sheet1 put this formula and copy down.........

=VLOOKUP(B2&C2&D2,Sheet2!A:F,2,false)

In cell F2 put this formula and copy down..........

=VLOOKUP(B2&C2&D2,Sheet2!A:F,6,false)


hth
Vaya con Dios,
Chuck, CABGx3
 
M

Mike

Thanks. I believe I see what it is that's being done, but I have a couple of
questions.

1) Could the column added to Sheet2 be added to the right of the current
columns so that it is not seen? Or, will I have to just shutdown the column
width?
2) Please breakdown the VLOOKUP commands you've used and explain what each
piece does. I can pick out some of it but not all of it.

Mike
 
T

Thansal

I Just wanted to thank CLR for this explenation, I was needing to do
something very similar.
 
C

CLR

VLOOKUP must have it's lookup column o the left side of the datarange. There
are ways to get around this, using INDEX and MATCH, but I'm not too versed on
them.

The formula: =VLOOKUP(B2&C2&D2,Sheet2!A:F,2,false)

is looking up the CONCATENATION of B2 and C2 and D2 in the Range specified
as Sheet2 columns AtoF....it then steps over 2 columns, (counting the coluns
it started in as 1) and returns the value in that cell (the partnumber being
sought).........the other formula is the same, except it steps over 6 columns
to return the Comment being sought......

If you want to "hide" the Lookup column, you can always change the font
color to the same as the background color.......

hth
Vaya con Dios,
Chuck, CABGx3
 
C

CLR

You're quite welcome Thansal, thanks for the thanks...........

Vaya con Dios,
Chuck, CABGx3
 
M

Mike

Thanks again. I belive this will work for now. I do see things getting more
complicated down the road though. I'll just have to wait and see where this
thing goes as we populate it.

Mike
 
Top