Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|