2 columns on master sheet:
col1 col2
Apple Fruit
Spinach Veg
Orange Fruit
Potatoe Veg
Pear Fruit
Bannana Fruit
Carrot Veg
Potataoe Veg
Say I wanted to copy all Fruit
data to a another sheet and all Veg
data to a 3rd sheet. Is there a way to do this? Does it require a macro or can I somehow do a VLOOKUP
?
Also I need this to be dynamic, so if a new row is added the corresponding sheets are updated.
You could use this horrible monstrosity of a formula:
=INDEX($B$2:$B$9, SMALL(IF("veg"=$A$2:$A$9, ROW($A$2:$A$9)-MIN(ROW($A$2:$A$9))+1, ""), ROW(A1)))
Enter it as an array formula with Ctrl+Shift+Enter.
I found the formula here: http://www.get-digital-help.com/2009/10/25/how-to-return-multiple-values-using-vlookup-in-excel/
Which might not be the best version or most elegant version of this formula(it is a common formula task) but it will get the job done.