hi,
extract data from sheet(all) to sheet(invoice) based on criteria sheet(invoice").range(c18) according to sheet(all).range(a2:a1000)
sample uploaded
hi,
extract data from sheet(all) to sheet(invoice) based on criteria sheet(invoice").range(c18) according to sheet(all).range(a2:a1000)
sample uploaded
Use Code-Tags for showing your code :
Please mark your question Solved if there has been offered a solution that works fine for you
If You like solutions provided by anyone, feel free to add reputation using STAR *
In B26
=IFERROR(INDEX(ALL!$B$5:$B$1000,SMALL(IF(ALL!$A$5:$A$1000=$C$18,ROW(ALL!$B$5:$B$1000)-ROW($A$5)+1,""),ROWS($A$5:A5))),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Copy down
You should now be able to complete all the other columns.
=IFERROR(AGGREGATE(15,6,ROW(ALL!$A$5:$A$8)/(ALL!$A$5:$A$8=$C$18),A26)-4,"") s and INDEX s
Thank you Jhon
just few questions
1 = why you use from row 5 (ALL!$B$5:$B$1000) while i need to start from row 2 = ALL!$B$2:$B$1000,
2 if i change range to ALL!$B$2:$B$1000 then what i change in this part -ROW($A$5)+1,""),ROWS($A$5:A5))) and what is the function of this part
should be
=IFERROR(INDEX(ALL!$B$3:$B$1000,SMALL(IF(ALL!$A$3:$A$1000=$C$18,ROW(ALL!$B$3:$B$1000)-ROW($A$3)+1,""),ROWS($A$3:A3))),"")
my error!!!!
... I noticed Tim did the same (your posted file was positioned at row 5 so we both took that as the start!)
thank you john
its working perfectly
and tim you too
With Tim's: range should be A3:A1000 not A2: all formulae need to be changed accordingly and will work.
Row 2 is header row, not first data row,
Dear Harron, Firstly remove merged cell.
In "B26"copy across.Formula:Please Login or Register to view this content.
File attach.
atul
If my answer (or that of other members) has helped you, please say "Thanks" by clicking the Add Reputation button at the foot of one of their posts.
Also: if your problem is solved, please take the time to mark your thread as SOLVED by going to the top of your first post, selecting "Thread Tools" and then "Mark thread as solved".
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks