Looking for formula to extract all values in range A7:A22 of sheet named Products to another sheet named Extract. See attached sample file.
Thanks
Looking for formula to extract all values in range A7:A22 of sheet named Products to another sheet named Extract. See attached sample file.
Thanks
Could you please explain why copy and paste would not be a suitable solution?
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Values changes from time to time. Wants formula driven approach to eliminate manual work. Thanks
So it's simply reading the data across, is it? When you say values change, what exactly do you mean? Will the list grow? Will rows sometimes be deleted from anywhere in the range? All of these things will affect any solution offered.
Yes, the list will grow. Rows will be deleted. Yes, values will change.
Enter this array formula in cell A4 and copy across and then down
Formula:=IFERROR(INDEX(Products!A:A,SMALL(IF((Products!$A$7:$A$22=Products!$A$2)+(Products!$A$7:$A$22=Products!$A$3)+(Products!$A$7:$A$22=Products!$A$4),ROW(Products!$A$7:$A$22)),ROWS(A$4:A4))),"")
***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.
Last edited by AlKey; 08-27-2016 at 11:55 AM.
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
OK, then the formula provided in post #5 will not work for you - I have tested it by deleting a row from the source data, and it fails at that point. I am sure that AlKey will have a better idea - I'll have a think about it, too.
Okay. Will be on the lookout for a working solution. Thanks
XOR LX: great...works. Can this also start from Products!A7:A22 instead of Products!A:A? Would like it to start from Products!A7:A22
No. You misunderstand. It is currently calculating over rows 7-22 only.
Although this is not the only method, AlKey's decision to use Products!A:A as the array to pass to INDEX is a good one, since this way we reduce the construction required to pass as the row_num parameter.
Regards
Okay - thanks XOR LX. Nice feedback. Testing now.
Please do not send PMs to members pestering them for help - this is in breach of forum rule #4:
I am going offline now for the evening. I am sure someone will be able to help. I shall be back online tomorrow morning.4. Don't Private Message or email Excel questions to moderators or other members. (or Word, Access, etc.) The point of having a public forum is to share solutions to common (and sometimes uncommon) problems with all members.
AliGW: thanks for making me aware of rule #4. Enjoy the rest of your evening.
Thanks. Hope you get a solution!
Don't change the first reference after the INDEX function - leave it as Products!A:A.when I changed it to
=IFERROR(INDEX(Products!$A$7:$A$21,SMALL(IF((Products!$A$7:$A$21=Products!$A$2)+(Products!$A$7:$A$21=Products!$A$3)+(Products!$A$7:$A$21=Products!$A$4),ROW(Products!$A$7:$A$21)),ROWS(A$4:A4))),"")
You're welcome. Please don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
You're welcome!
Likewise!
Cheers
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks