+ Reply to Thread
Results 1 to 9 of 9

Sort/Filter/Lookup

  1. #1
    Mike
    Guest

    Sort/Filter/Lookup

    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


  2. #2
    CLR
    Guest

    RE: Sort/Filter/Lookup

    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



    "Mike" wrote:

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


  3. #3
    Mike
    Guest

    RE: Sort/Filter/Lookup

    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


    "CLR" wrote:

    > 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
    >
    >
    >
    > "Mike" wrote:
    >
    > > 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
    > >


  4. #4
    CLR
    Guest

    RE: Sort/Filter/Lookup

    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



    "Mike" wrote:

    > 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
    >
    >
    > "CLR" wrote:
    >
    > > 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
    > >
    > >
    > >
    > > "Mike" wrote:
    > >
    > > > 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
    > > >


  5. #5
    Mike
    Guest

    RE: Sort/Filter/Lookup

    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

    "CLR" wrote:

    > 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
    >
    >
    >
    > "Mike" wrote:
    >
    > > 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
    > >
    > >
    > > "CLR" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > >
    > > > "Mike" wrote:
    > > >
    > > > > 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
    > > > >


  6. #6
    CLR
    Guest

    RE: Sort/Filter/Lookup

    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


    "Mike" wrote:

    > 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
    >
    > "CLR" wrote:
    >
    > > 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
    > >
    > >
    > >
    > > "Mike" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "CLR" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > >
    > > > > "Mike" wrote:
    > > > >
    > > > > > 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
    > > > > >


  7. #7
    Thansal
    Guest

    Re: Sort/Filter/Lookup

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

    Mike wrote:
    > 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
    >
    > "CLR" wrote:
    >
    > > 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
    > >
    > >
    > >
    > > "Mike" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > > "CLR" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > >
    > > > > "Mike" wrote:
    > > > >
    > > > > > 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
    > > > > >



  8. #8
    CLR
    Guest

    Re: Sort/Filter/Lookup

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

    Vaya con Dios,
    Chuck, CABGx3



    "Thansal" wrote:

    > I Just wanted to thank CLR for this explenation, I was needing to do
    > something very similar.
    >
    > Mike wrote:
    > > 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
    > >
    > > "CLR" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > >
    > > > "Mike" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > > "CLR" wrote:
    > > > >
    > > > > > 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
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Mike" wrote:
    > > > > >
    > > > > > > 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
    > > > > > >

    >
    >


  9. #9
    Mike
    Guest

    RE: Sort/Filter/Lookup

    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


    "CLR" wrote:

    > 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
    >
    >
    > "Mike" wrote:
    >
    > > 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
    > >
    > > "CLR" wrote:
    > >
    > > > 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
    > > >
    > > >
    > > >
    > > > "Mike" wrote:
    > > >
    > > > > 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
    > > > >
    > > > >
    > > > > "CLR" wrote:
    > > > >
    > > > > > 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
    > > > > >
    > > > > >
    > > > > >
    > > > > > "Mike" wrote:
    > > > > >
    > > > > > > 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
    > > > > > >


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1