Automatically updating lists of data on to separate sheets

I have a list/s of names and details and i would like to automatically update the list on to new sheets after the criteria has changed.

So for example, if opened = No, i'd like the row/s and column's of data to automatically update onto a new sheet, Column A = Name, Column B = Date, Column C = Details Required, Column D = Opened.

I've tried Vlookup but can only get one piece of data across, so i thought i'd bow to your greater knowledge. If you can help that would be great and also if you need to ask me any questions for more details i'll gladly answer, if i can!

Thanks

Ian

Hi, and welcome to the forum,

Have you checked out Data Filter Advanced Filter, using the copy to another location option?

This will undoubtedly meet your requirement.

HTH

No i haven't, i'll give that a try and see how it goes, just put data in a table and then copy the data to another sheet.

Would this automatically update once the data i have previously filtered changes?

Thanks for the welcome!

Hi,

No, you'd need to perform the filter again if you change the data. The best way to proceed is to use a simple macro which will automatically filter the data for you when the data changes.

Copy the field headings from your data that you want to filter to a new sheet, (sheet2 in the example below) say cells A10:E10, and name that range of headings "DataOut"

Now also on sheet2, enter the criteria you want to use by putting a field heading in say A1 and underneath it in A2 the value of the item from that field that you want to filter, and name A1:A2 "FilterCrit"

Assuming your data starts in A1 on sheet1 and is in a contiguous range, the following macro will filter for you.

Please Login or Register  to view this content.

Now run the macro. Change the A2 cell on sheet2 as appropriate to filter for different values. If you need more than one criteria to be filtered, just add them to B1:B2, C1:C2 on sheet2 as appropriate.

HTH