Thursday, January 2, 2014

yogi_Merge Data From Several Sheets And Use QUERY Function To Extract Records Where First Column Is Not Blank

                                          Google Spreadsheet   Post  #1466
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI   Jan-02-2014
post by ET153 (!category-topic/docs/spreadsheets/rTLA9fnYG9U)
VMerge, Query, and joining multiple sheets within one workbook
I have been working on this for a few hours now and can't seem to get it to work properly... Here is what I'm trying to do.

I have a workbook with multiple Sheets (currently 4 Sheets and one Master).

This is for tracking individuals and I'd like for each employee to update their own (I've already addressed permissions) and the boss would be able to view the 'Master' Sheet which would allow him to get his statistics and view employee progress.  There is the potential for hundreds of names but likely less than 100 per Sheet during typical work.

Link to simulated (actually a copy) of document:

This is what I have in the Master Sheet A2 (in an effort to pull the information to this sheet)

=Vmerge(QUERY('EThompson'!A2:G); QUERY('Employee2'!A2:G); )

I tried adding the other sheets to Master and it works but I get about 100 blank spaces per sheet added to the master sheet. I tried the 'select * where Col1 is not null' after the final Query but I get an error through Vmerge ('All data ranges must be of equal width!'). I have tried several recommendations from other posts using importrange, having Query before VMerge and I can't seem to get the data to the Master sheet without the blank cells. My goal is to have each name and associated data merged to the Master sheet, without blank cells, and to be able to use filter so the boss can see each school, grade, alphabetical, etc and get the statistics he needs.

Clear as mud? Thanks for the help, I'm stuck.