I suspect you can adapt the set up Post #3 in: Multiple vlookup's?.
This is a discussion on Selecting specific lines from a list depending on criteria within the Excel Questions forums, part of the Question Forums category; Hi all, I am looking for a formula to only copy "Ordered" status parts from a list So on sheet ...
Hi all,
I am looking for a formula to only copy "Ordered" status parts from a list
So on sheet 1 i have
Col A
Quantity
Col B
Part NUmber
Col C
Description
Col D
Status
On SHeet 2 i would like all "Ordered" lines to show
What sort of formula could i use in order to achieve my "Ordered" list on sheet 2
Many Thanks
I suspect you can adapt the set up Post #3 in: Multiple vlookup's?.
Assuming too much and qualifying too much are two faces of the same problem.
Excel 2010
A B C D E 1 Quantity Part NUmber Description Status 2 1 a dd ordered 3 2 a se 4 1 s ww ordered 5 3 d ww 6 7 8 9 Sheet1
Excel 2010
A B C D E F G H 1 Quantity Part NUmber Description Status ordered 2 1 a dd ordered 3 1 s ww ordered 4 5 6 Sheet2
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula A2 {=IFERROR(INDEX(Sheet1!A$2:A$5,SMALL(IF(Sheet1!$D$2:$D$5=$H$1,ROW(Sheet1!$D$2:$D$5)-ROW(Sheet1!$D$2)+1),ROWS(Sheet1!$D$2:D2))),"")}
Note: Do not try and enter the {} manually yourself
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Hi Robert Mika,
That is just fantastic work!
I have modified to suit my ranges and criteria and works perfect.
I have one more question though. Can this be set to display not only "Ordered" but "Partially Shipped" aswell
Many thanks for you time
There few ways to achive that
For two categories only :
Excel 2010
A B C D E F G H 1 Quantity Part NUmber Description Status ordered 2 1 a dd ordered Partially Shipped 3 1 s ww ordered 4 3 d ww Partially Shipped 5 6 7 8 9 Sheet2
Array Formulas Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Cell Formula A2 {=IFERROR(INDEX(Sheet1!A$2:A$5,SMALL(IF((Sheet1!$D$2:$D$5=$H$1)+(Sheet1!$D$2:$D$5=$H$2),ROW(Sheet1!$D$2:$D$5)-ROW(Sheet1!$D$2)+1),ROWS(Sheet1!$D$2:D2))),"")}
Note: Do not try and enter the {} manually yourself
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Simply perfect, many thanks for your time and help!
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Hi Robert,
I have encountered a slight problem with the "Partially Shipped" Lines
Sheet 2 has a simple formula that generates the "Ordered" or "Partially Shipped" lines
The formulas you have created show the originally ordered on the backorders under partially shipped and not the "Outstanding Balance"
Sheet 2 houses the simple formulas in Column R which generate the "Ordered" and "Partially Shipped" with column Y showing what has been recieved
The formula is
=IF(B16="","",IF(B16=Y16,"Shipped",IF(OR(Y16="",Y16=0),"Ordered",IF(Y16
Would there be a way to show the outstanding balance for "Partially Shipped" lines instead of the ordered quantity
Many Thanks
- Read the Posting Guidelines, Forum Rules & FAQs
- Want to post a small screen shot? Try one of these Excel jeanie, MrExcel HTML Maker or Borders-Copy-Paste
- If posting vba code, please use Code Tags .
Like this thread? Share it with others