Tuesday, March 19, 2013

yogi_Create A MasterList of All Active Jobs From All Of The Yearly Lists

                                          Google Spreadsheet   Post  #1088
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 20, 2013
user Kyle Korte :(https://productforums.google.com/forum/#!category-topic/docs/presentations/how-do-i/desktop/1QDc8jNqO5k)
Windows 7 ; Chrome

Ok here it goes.  I have created a job list in a workbook with different sheets for each year the job started in.  I then have a master list that I want to pull in all of the active jobs from each of the year sheets.  I use this master list with an import function to feed other workbooks that need a drop down list of all current jobs (time cards, to do lists, ect).  I am having a problem figuring out how to get information from multiple sheets to popluate the active list.  I am pretty sure Vmerge is the way to go but I cant seem to find the right characters.  The last line I attempted was 

=VMERGE(filter ( '2013'!B3:B; ('2013'!E3:E = "In Progress") ));filter('2012'!B3:B('2012'!E3:E:"In Progress"))

here is the link to a dummy sheet that illustrates what I am going for:

Thanks for any help you can provide!
--------------------------------------------------------------------------------------------------------------------------------

following is a solution to the problem

1 comment:

  1. Amazing Yogi, thank you for the help it works perfectly. I think I am getting a better grasp of the "how" in this the only thing I I dont understand in the function (=query(vmerge('2012'!A2:E;'2013'!A3:E;'2014'!A3:E);"select Col1,Col2,Col3,Col4 where upper(Col5)='IN PROGRESS' ")) is the use of upper, what does that denote. Thanks again for your invaluable help.

    ReplyDelete