Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
Hello all, this is my first time of using this - so, hopefully someone can
help. I am looking to do the following: I have a part number - for instance 12345 I have a number of open purchase orders for that part - which are all listed on a rather large file with a number of other open purchase orders. What I want to do is take all the open purchase orders for that particular part and 'pull' them into another file. I would do this by looking up the part number and pulling each induvidual line item of information into another worksheet. Vlookup only takes the first value it gets to - how can I make it take the 1st, 2nd, 3rd lines of information? Thanks for your help. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
How will filter work, when the information is in one sheet and I want to pull it into another? I am doing this for 100+ parts, with 700+ purchase orders. I want to see each part's purchase orders below the part - without having to copy and paste each block of data from the source sheet into induvidual part sheets. -- mickyd67 ------------------------------------------------------------------------ mickyd67's Profile: http://www.excelforum.com/member.php...o&userid=35704 View this thread: http://www.excelforum.com/showthread...hreadid=554890 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
Assign a name to your purchase orders data (say: "orders")
Then you can use it (as =orders) in the source range of an advanced filter Criteria range must specify data label ("order_no") in first row, and desired value (say 1234) in second row Tick "Copy to another destination" Destination range must include desired column headers HTH -- AP "mickyd67" a écrit dans le message de news: ... How will filter work, when the information is in one sheet and I want to pull it into another? I am doing this for 100+ parts, with 700+ purchase orders. I want to see each part's purchase orders below the part - without having to copy and paste each block of data from the source sheet into induvidual part sheets. -- mickyd67 ------------------------------------------------------------------------ mickyd67's Profile: http://www.excelforum.com/member.php...o&userid=35704 View this thread: http://www.excelforum.com/showthread...hreadid=554890 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
Ardus - thanks for your help, however that isn't working. Either I am doing something wrong or I've not explained it correctly. What I am attempting is: Sheet 1 Cell A1 = part number 12345 Sheet 2 Column A = various part numbers (several hundred) Column B = PO number Column C = PO quantity Column D = PO due date Column E = PO required date Column F = PO actions Column G = Supplier name What I want to do is take the PO’s on sheet 2 that belong to part number 12345 and bring them into sheet 1 in a table that looks at the part number and pulls all the data that appears in columns B to G for that part. Some parts will have one PO – others could have dozens. Does your idea still work? Or is there another solution? (also just a note - I am doing this on a spreadsheet with 100+ tabs, 1 per part number and several hundred PO's. Therefore you can see why I am attempting this) Cheers. -- mickyd67 ------------------------------------------------------------------------ mickyd67's Profile: http://www.excelforum.com/member.php...o&userid=35704 View this thread: http://www.excelforum.com/showthread...hreadid=554890 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
In order to be able to use DataFilter, you need to have column headers
above your data Sheet1: ----------- A1 = "part_no" A2 = 12345 Sheet2: ----------- A1 = "part_no" A2 thru A999: your part numbers B1 = "PO_no" B2 thru B999: your PO no's etc... HTH -- AP "mickyd67" a écrit dans le message de news: ... Ardus - thanks for your help, however that isn't working. Either I am doing something wrong or I've not explained it correctly. What I am attempting is: Sheet 1 Cell A1 = part number 12345 Sheet 2 Column A = various part numbers (several hundred) Column B = PO number Column C = PO quantity Column D = PO due date Column E = PO required date Column F = PO actions Column G = Supplier name What I want to do is take the PO’s on sheet 2 that belong to part number 12345 and bring them into sheet 1 in a table that looks at the part number and pulls all the data that appears in columns B to G for that part. Some parts will have one PO – others could have dozens. Does your idea still work? Or is there another solution? (also just a note - I am doing this on a spreadsheet with 100+ tabs, 1 per part number and several hundred PO's. Therefore you can see why I am attempting this) Cheers. -- mickyd67 ------------------------------------------------------------------------ mickyd67's Profile: http://www.excelforum.com/member.php...o&userid=35704 View this thread: http://www.excelforum.com/showthread...hreadid=554890 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Vlookup only taking first line of data
if you mean what I think you mean you just repeat your vlookup actoss your
sheet increasing the column numbers upward as you go actoss until you fill alll coluns as required -- paul remove nospam for email addy! "Ardus Petus" wrote: In order to be able to use DataFilter, you need to have column headers above your data Sheet1: ----------- A1 = "part_no" A2 = 12345 Sheet2: ----------- A1 = "part_no" A2 thru A999: your part numbers B1 = "PO_no" B2 thru B999: your PO no's etc... HTH -- AP "mickyd67" a écrit dans le message de news: ... Ardus - thanks for your help, however that isn't working. Either I am doing something wrong or I've not explained it correctly. What I am attempting is: Sheet 1 Cell A1 = part number 12345 Sheet 2 Column A = various part numbers (several hundred) Column B = PO number Column C = PO quantity Column D = PO due date Column E = PO required date Column F = PO actions Column G = Supplier name What I want to do is take the POs on sheet 2 that belong to part number 12345 and bring them into sheet 1 in a table that looks at the part number and pulls all the data that appears in columns B to G for that part. Some parts will have one PO €“ others could have dozens. Does your idea still work? Or is there another solution? (also just a note - I am doing this on a spreadsheet with 100+ tabs, 1 per part number and several hundred PO's. Therefore you can see why I am attempting this) Cheers. -- mickyd67 ------------------------------------------------------------------------ mickyd67's Profile: http://www.excelforum.com/member.php...o&userid=35704 View this thread: http://www.excelforum.com/showthread...hreadid=554890 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Inserting a new line when external data changes | Excel Discussion (Misc queries) | |||
Macro data selection line delete | Excel Discussion (Misc queries) | |||
How can I get 7.3 million data pts on one line graph? | Excel Discussion (Misc queries) | |||
Formatting lines between data points in a line chart | Charts and Charting in Excel | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |