Tuesday, April 1, 2014

yogi_For Row By Row Project Description In Column A Pull Project Description Into Column B From Spreadsheet Sample 2

                                         Google Spreadsheet   Post  #1584
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-01-2014
post by Analyn Brazza-Stull (https://productforums.google.com/forum/#!mydiscussions/docs/mKn192bo4-Y)
IMPORTRANGE only working on first 250 rows loaded on the sheets
Hi all,

I currently am working on two separate spreadsheet.  Due to the importrange error I get because it's only importing data that is available in the first 250 rows of the spreadsheet I am trying to pull from, I need an app script code that states:

if A-6 = 0 (if there is no number inputted into a cell A-6 then it should just be empty)  If a number is inputed like L11114 then it should look up that number from another sheet in column B2:B14000 and look up the number in column B2 and input the data next to the number in C2 into B-6 on the original sheet.

Here is the formula I used originally on the sheet itself: =IF(A6=0, ,INDEX(IMPORTRANGE("0AjuRL6DSBmUHdGRCVV83UDlfbXFUQVNoM0k5bEhaYWc", "Dir-Master!B2:C1400"), MATCH(A6, IMPORTRANGE("0AjuRL6DSBmUHdGRCVV83UDlfbXFUQVNoM0k5bEhaYWc", "Dir-Master!B2:B1400"), 0), 2))

The formula works only if you were pulling from the first 250 rows that are loaded in the Google spreadsheet but it won't pull any numbers that aren't on the initial sheet on the spreadsheet.  And the data and information is growing in numbers everyday.

Current Sheet - when I input the number L11114 it should look up L11114 from the Other sheet and pull "Sandstorms" from that sheet and auto-populate into Column B on my current sheet.
   Column A               Column B
   Proj #                     Proj Description
L11114                   Sandstorms

Other sheet
Column B              Column C
L11112                  Hurricanes
L11113                  Tsunamis
L11114                  Sandstorms