LinkBack URL
About LinkBacks
Hi I am trying to extract certain data from a main spread sheet to create a statement. Pls see attached example.
Hi I am trying to extract certain data from a main spread sheet to create a statement. Pls see attached example.
Please see attached file with array formula.Originally Posted by arytxer
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thank you for this but it will not allow me to expand the formula to add more rows, every time I try, the cells goes blank.
I have a massive spreadsheet with over 1000 rows, is it possible you could help me to understand why it wont allow me to add rows please thanks.
The suggested formula is an ARRAY formula...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. Press F2 on that cell and try again.
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
AlKey, a quick question. In your formula...
=IFERROR(INDEX(B$2:B$16,SMALL(IF($A$2:$A$16=$A$20,ROW($A$2:$A$16)-ROW($A$2)+1),ROWS(A$2:A2))),"")
In the bolderd part, could that be changed to just -1?
=IFERROR(INDEX(B$2:B$16,SMALL(IF($A$2:$A$16=$A$20,ROW($A$2:$A$16)-1),ROWS(A$2:A2))),"")
Well, it will make a difference if the OP decides to insert a row or two. Adding -ROW($A$2)+1) makes formula more robust.
Thanks guys this works perfectly so far.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks