Help with Advanced Filters and Auto Refresh

[tjd1159]

I have 5 worksheets in a workbook, each with a table containing the same column headings and all tables begin at B12 and end at Col L. I would like to have the rows from each of the 5 tables copied to a separate master sheet. Also, each time the tables are updated with new data and/or rows are added/deleted I would like the master to automatically refresh. I suspect I need to use Advanced Filter and some VBA code but I'm am a novice with Excel. I have experimented with the Advanced Filter and understand it a little. Any help would be appreciated!

 

[ravinder pabby]

Can you please elaborate with example. Do you want summary sheet having sum of rows from 5 sheets or something else.

 

[Simon4s]

Did you want the master sheet to have the same number of rows and columns as the 5 worksheets?

 

[tjd1159]

Thank you for your replies. I would like to copy every table row from all 5 sheets into a master sheet so a manager can see all the active projects at a glance. When a person updates their sheet, I would like the master to automatically update. The master will have the same columns as the 5 sheets. Unfortunately, it appears I don't have the right to attach a sample. I'll try to describe the workbook.

Sheets 1 to 5 contain a person's name and an identically formatted table containing each person's personal list of active projects.
Master sheet has same column headings and displays every project from the 5 sheets. Ideally I would like to break the list into sections headed by each person's name. I hope this helps.

 

[ravinder pabby]

if 5 sheets have fixed no of rows and columns then it is very simple task of pasting link of tables in master.

Do the following

Select the table fron sheet 1 and press [Ctrl]+C.

Select a target cell in master and choose Paste Link from the Paste drop-down.

Similarly repeat the steps for other 4 sheets
If 5 sheets do not have fixed no of rows then there may be some other solution using vba.

 

[tjd1159]

Thank you Ravinder. I had discovered linking but the rows will increase and decrease regularly so that won't work for me. I may need a vba solution but the examples I've found so far have not helped either.

 

[Simon4s]

Thank you Ravinder. I had discovered linking but the rows will increase and decrease regularly so that won't work for me. I may need a vba solution but the examples I've found so far have not helped either.

So you want all sheets including the master sheet to look the exact same with the same number of rows and columns?

First, Align all the 5 sheets perfectly (make sure that the first row is empty)
Make a master sheet. Select b1> select data > select data validation >data validation again > list > type in Amanda, Ted, Bruno, Jack, ***.
Now, in A2, "=If(b1="bruno", bruno!A2,if(b1="Amanda",amanda!A2,if(b1="ted",ted!a2,if(b1="jack",jack!A2))),"")....
Apply this to all cells.

You will have a master copy that looks at all 5 names individually.