Extracting and pasting a row based on a value HELP!

New Contributor

I have a fairly basic level of excel but not even google can help me with this one.


Without going into too much detail, I have a large table which has columns titled department, description, impact etc. and further along I have one's titled with different areas that could be effected by such impacts, if an area is effected by the individual impact there is a 'Y' underneath.


Basically, I need to extract the data from a row if it contains a Y under a particular column and paste it into a new table. Importantly this table should update depending on the original table, e.g. if a Y from the particular column is removed from the main table, so should the row in the new table.


Can anyone help me with this?

5 Replies


It would help if you could provide much more detailed information, and preferably attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Thanks in advance.

@Hans Vogelaar 

I have attached a replica table. Essentially, what I am trying to do is to extract the entire row of data if it affects (in this example) the Directors and paste it into another table. This separate table contains all the details (reference, owner, description etc.) as the original except only for rows that impact the Directors. Also, I am unsure how to create this new table so that it updates from the original table --> like if IM-01 was decided to impact the directors, simply adding a Y underneath such column would update it. 


As I said, my skills on excel are fairly basic but I hope I am being clear, thanks for your help  Dropbox 


If you have Microsoft 365 or Office 2021, you can use the FILTER function. See the attached version.

@Hans Vogelaar Thankyou, but how would I make it so each table updates each other if either one is edited? At the moment if I edit the copied data, an error message appears


That would be too complicated. You'd be better off filtering the data on Sheet1, as in the attached version.