Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasting Information from Multiple Cells
Hi,
I have a report that shows all open purchase order lines by part number. The information shows the estimated delivery date and quantity. Some part numbers have multiple lines due to different arrival dates. For each Part number I would like to paste all the delivery dates into one cell on another spreadsheet. Is this possible? If so how do I do it? Thank you, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Pasting Information from Multiple Cells
I believe a User Defined Function (UDF) would work for this situation. Place
the code for the UDF into a code module and then use it in cells just as you would any other worksheet function. This code presumes that your source list is on 'Sheet1' and that the part numbers are in column A, with delivery dates in column B. Change code to fit reality. Private Function BuildLongList(TestValue As Variant) Dim EndOfList As Long Dim SourceRange As Range Dim LC As Long Application.Volatile Set SourceRange = Worksheets("Sheet1").Range("A1") EndOfList = Worksheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row Do While LC <= EndOfList If SourceRange.Offset(LC, 0) = TestValue Then BuildLongList = BuildLongList & SourceRange.Offset(LC, 1) & ", " End If LC = LC + 1 Loop If Right(BuildLongList, 2) = ", " Then BuildLongList = Left(BuildLongList, Len(BuildLongList) - 2) End If Set SourceRange = Nothing ' release End Function Then in a cell on the other sheet (or same one) where you want the list to appear enter a formula similar to this: =BuildLongList('Sheet1'!A1) ' collate all dates for P/N in A1 on Sheet1 "Richard" wrote: Hi, I have a report that shows all open purchase order lines by part number. The information shows the estimated delivery date and quantity. Some part numbers have multiple lines due to different arrival dates. For each Part number I would like to paste all the delivery dates into one cell on another spreadsheet. Is this possible? If so how do I do it? Thank you, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
keeping cells locked and protected when copying and pasting | Excel Discussion (Misc queries) | |||
Pasting a formula in multiple cells without changing the range | Excel Discussion (Misc queries) | |||
a function that counts the amount of cells with information in them | Excel Discussion (Misc queries) | |||
How can I have excel search and add multiple cells to find a targe | Excel Discussion (Misc queries) | |||
Pasting single cells from Word to multiple cells in Excel | Excel Worksheet Functions |