get data from a table based on a columns value into another table

Copper Contributor

I've got several sheets that contain tables with date that I want to be able to pull from into another sheet based on the number of days left. Each sheet is there own topic and the data pulled is the overdue. I've tried advance filter but it doesn't add rows to the output table if needed, it just places the information on the outside of the table. It also doesn't allow for another table to be below another one. I've tried attaching a test file for a better description but it didn't work. Tried my best to explain what I'm trying to do. Hopefully someone can help me.

6 Replies

@Daniel_Morales 

You can try the attached file with Power Query. Enter the number of days in cell G3 and then refresh the query.

days.JPG

@OliverScheurich I don't think power query works for what I'm trying to do. Tried attaching an example file but I keep getting an .xlsx not supported error on here. I'll try and explain the example layout.

Sheet 1 is the output sheet labeled overdue; it contains 3 tables: OPSEC, INFOSEC, EO; each table has the same headers: team, name, days left.

Sheet 2, 3, and 4 each have a table with the same headers containing the same teams and names; only difference is the numbers of days left. 

Each table in sheet 1 should pull the team, name, and days left for the personnel that have less than 30 days left from their respective table/sheet with the same title. Also, the tables in sheet 1 need to be able to add and remove rows based on data pulled. For example if there are no under 30 days left than the table in sheet 1 should only have 1 row. 

Advance filter did not work because the tables in sheet one are under each other. Power Query seems to be for specific number and not range of numbers, unless you can set it to pull a range of numbers and I just couldn't figure it out.

Hope this was a better explanation of what I'm trying to do. Attaching my example file would've been easier.

@Daniel_Morales 

You can try the attached file with macros. In cell I1 you can enter the filter criteria. Then you can click the button in cell G2 to run the macro. You can click the button in cell G4 to reset the sheet in order to select a new filter criteria and to run the macro again.

Thank you, this definitely on the right track of what I'm trying to do. Is there a way to turn everything into a table format? I converted everything into a table and ran the filter and it seemed to work, it kept everything in a table; when I ran reset though it cleared everything to include the tables I turned everything into. Only ask because I need to be able to add and remove personnel and it is easier to do when everything is in a table.

@OliverScheurich https://drive.google.com/file/d/1tCNXkfyM9aLwiN6_6NpgrIsIZtp2Ivnh/view?usp=sharing Here is the link to the file I'm working on. I really tried to make it work but can't get it to filter to the right tables and reset the tables correctly. Could really use your help.

@Daniel_Morales 

You can try the attached file. Maybe this is similar to what you are looking for. In your sheet i can only view the sheets and can't view or change the code.