Wednesday, August 15, 2012

yogi_For Project Number Entered In Cell B2 Extract In Cell E2 Client Approval Date From Project Master Spreadsheet

Yogi Anand, D.Eng, P.E.       Google Spreadsheet    Post  #699   Aug 15, 2012

user outlaw26r said: (!category-topic/docs/spreadsheets/uOqfBZWhqFY%5B1-25%5D)
import range cell if it matches project number from another sheet
I am creating a project cover sheet document that I would like to pull data from various Google spreadsheets so the project team can get a good quick feel for the project status. My goal is that when I begin a new project, I am able to type in the unique project number and have it auto populate from various sources.
I would like to pull in the project information from G2 (1/3/13) in the example below, which I am currently able to do with the formula:
=ImportRange("spreadsheetA","Running Master!G2") 
However, I would like to put the logic in so that the formula looks at B2 from the destination spreadsheet for the unique project number and use that information to find the matching number in spreadsheetA and then grab the info from cell G in that Row. 
I appreciate any help as I am a bit stumped on how best to proceed or which to attempt first.

         A            B              C                D                           E                                F                       G               
      Project #        Client        Project Name      Assigned To:      Asset Collection Date                Approval Date           Client Delivery Date
 1     0177            BBQ         Smoked Ribs       Intern                          12/12/12                                    12/13/12                    12/14/12
 2     0178             Fish            Salmon                 Jake                              1/1/13                                          1/1/13                         1/3/13
My issue is I am not sure how to combine the formulas to use the logic properly so it may be best to layout the pieces as I understand them. I have included again for connivence the two sample spreadsheets used;
Project Data Sheet Template   &    test project master sheet
So for example, I have put into my <Project Data Sheet Template>"cover sheet!E2"  the formula:

=ImportRange("0AnOQrs4bVHv0dGNncFNnaGRuVXBEdU8waXJGVGtyX3c","running master!H8")
This gives me the desired result of 8/15/2012.
My goal is for the formula to arrive at the H8 by using the logic, (forgive me if it isn't the correct formulas to explain)
  1. Search cell <Project Data Sheet Template>"cover sheet!B2
  2. Use data returned from <Project Data Sheet Template>"cover sheet!B2 to search for matching data (in this case 0177) in <test Project Master Sheet>,"Running Master" in column B:B and return that row.
  3. Filter that row so it only returns the cell in the H column to <Project Data Sheet Template>"cover sheet!E2 . Which I just learned how to filter out unwanted columns from you last night, just unsure of how the formula would be applied in the larger formula to process in the right order. Still learning that part.
Again, Yogi, thank you so much for your time and efforts. I hope this time I explained it enough that it will give you something to work with. I See you answering a lot of forum questions I have stumbled across, and appreciate how you explain how the formula works in such a way as I can learn from it. 
Thank you,


following is a solution to the problem

here is an image of Project Master Spreadsheet

No comments:

Post a Comment