Extract columns from tables onto different sheet

[mcbellmo]

I have a workbook with numerous different spreadsheets that each have a table containing data from different samples. The tables have the same columns and look the same, just different data. Now I want to take say column x and column y from every table and make one large column x and one large column y on a seperate sheet containing data from all the other sheets. I need this so I can graph it and get a best fit line for all the different samples. The problem is, the tables on the other sheets can grow because this spreadsheet is active and users will input new data as time goes on. Also, the compiled columns x and y on the summary sheet cannot have blank spaces, only numbers, because I also need to reference these growing columns in a formula to get the best fit line coefficients which I will be using for another formula. I feel like I've tried everything, please help.

 

[Deuce Bucksman]

Have you tried setting up a summary page and duplicating the tables, and then link each sheet to the summary page. If they are exactly the same, all you have to do autofill? This way it will remain dynamic in the event someone else inputs new data.

 

[mcbellmo]

I'm not quite sure what you mean. Do you mean duplicate as in copy and paste? Because I can't paste the table as a link. And what do you mean by autofill?

 

[mcbellmo]

If anyone knows how to take a column of values and make an array out of it with name manager excluding zeroes then I could do it.

 

[Deuce Bucksman]

Sorry for the delay: To clarify, you indicated that the tables were exactly the same. So you can copy the table (not the links, just the format of the table as values in a seperate worksheet). Link once cell to all the tables, then drag using the scissor at the base of the cell accross the entire table (autofill). I might not follow and if that is the case I apologize. I just know that if tables are identical across several spreadsheets, that is the method I use.

 

[mcbellmo]

Oh ok I see what you mean but I was looking for something completely automatic so that the user wouldn't have to do anything except enter data on the different sheets and the summary sheet with the summary table, graph, and best fit line would update automatically. So I don't think the autofill would work because you mean using autofill every time you enter new data right?

 

[mcbellmo]

Remember the tables are getting bigger with time. They are not all the same size only the same column headers.