Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
I am creating a Spreadsheet to keep track of orders placed, and want to
create a front sheet to list all orders with the same Purchase order Number or invoice number. I know the Vlookup brings you a specific line but is there a formular which can allow you to bring up all the lines whith this number in? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
Alistaire,
As you say returning the first instance is done with vlookup. Second and subsequent instances canj be returned with a macro or using index:- =INDEX(Sheet1!$A$3:Sheet1!$F50,SMALL(IF(Sheet1!$A$ 3:Sheet1!$F50=$A$5,ROW(Sheet1!$A$3:Sheet1!$F50)-ROW(Sheet1!$A$3)+1,ROW(Sheet1!$F50)+1),2),2) The above checks sheet1 (change as req'd) in the leftmost column for the value in A5 (all similar to Vlookup up to now). The last 2 tells the formula to return the value from column 2 and the second to last tells it to return the second instance of the criteria. Change this for th3 3rd etc. It's and array so CTRL+ Shift+enter "Alistaire Green" wrote: I am creating a Spreadsheet to keep track of orders placed, and want to create a front sheet to list all orders with the same Purchase order Number or invoice number. I know the Vlookup brings you a specific line but is there a formular which can allow you to bring up all the lines whith this number in? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Formula
thanks i will give that a try
"Mike H" wrote: Alistaire, As you say returning the first instance is done with vlookup. Second and subsequent instances canj be returned with a macro or using index:- =INDEX(Sheet1!$A$3:Sheet1!$F50,SMALL(IF(Sheet1!$A$ 3:Sheet1!$F50=$A$5,ROW(Sheet1!$A$3:Sheet1!$F50)-ROW(Sheet1!$A$3)+1,ROW(Sheet1!$F50)+1),2),2) The above checks sheet1 (change as req'd) in the leftmost column for the value in A5 (all similar to Vlookup up to now). The last 2 tells the formula to return the value from column 2 and the second to last tells it to return the second instance of the criteria. Change this for th3 3rd etc. It's and array so CTRL+ Shift+enter "Alistaire Green" wrote: I am creating a Spreadsheet to keep track of orders placed, and want to create a front sheet to list all orders with the same Purchase order Number or invoice number. I know the Vlookup brings you a specific line but is there a formular which can allow you to bring up all the lines whith this number in? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|