I am trying to find all of the Imprint Orders on December18 and create a list on Sheet2 using the array formula
=IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,ROW(December18!$D$1:$DX$5)-ROW(December18!$D$1)+1),ROWS(A$3:A3))),””)
I get 15 results but they are all of the first order which is not marked "Imprint".
Looks like you have rows and columns confused, try this instead
=IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,COLUMN(December18!$D$1:$DX$1)-ROW(December18!$D$1)+1),ROWS(A$3:A3))),"")
That kind of worked. I returned 16 records but only 5 of them are correct. The other 11 are not marked "imprint".
Formulas in A4, B4, C4 then dragged down.
=IFERROR(INDEX(December18!$D$1:$DX$1,AGGREGATE(15,6,COLUMN(December18!$D$1:$DX$1)/(December18!$D$5:$DX$5=$B$2),ROWS(A$4:A4))-COLUMN(December18!$D$1)+1),"")=IFERROR(INDEX(December18!$D$4:$DX$4,AGGREGATE(15,6,COLUMN(December18!$D$1:$DX$1)/(December18!$D$5:$DX$5=$B$2),ROWS(B$4:B4))-COLUMN(December18!$D$1)+1),"")=IFERROR(INDEX(December18!$D$62:$DX$62,AGGREGATE(15,6,COLUMN(December18!$D$1:$DX$1)/(December18!$D$5:$DX$5=$B$2),ROWS(C$4:C4))-COLUMN(December18!$D$1)+1),"")
Pl note
Array formula should be confirmed with Ctrl+Shift+Enter keys together.
If answere is satisfactory press * to add reputation.
Sorry, there were 2 row references thta should have been changed to column, I thought that I had done both, but missed the second one.
=IF(ROWS(A$3:A3)<=$C$1,INDEX(December18!$D$1:$DX$1,SMALL(IF(December18!$D$5:$DX$5=$B$1,COLUMN(December18!$D$1:$DX$1)-COLUMN(December18!$D$1)+1),ROWS(A$3:A3))),"")
That worked!! Thank you so much!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks