+ Reply to Thread
Results 1 to 8 of 8

Auto Fill Formula into New Rows/Columns [Google Sheets]

  1. #1
    Registered User
    Join Date
    04-27-2016
    Location
    San Diego, CA
    MS-Off Ver
    14.6.1
    Posts
    6

    Auto Fill Formula into New Rows/Columns [Google Sheets]

    I am using Zapier to pull data from Typeform and place into new rows in a spreadsheet. The formulas in the columns take the data in the Typeform columns and manipulate them. However as a new row is added, the formula columns are also blank. I want the forumulas to autopopulate in new rows when those cells are blank.

    | A | B | C | D | E | F |

    1 | Name | email | score | calc 1 | calc 2 | calc 3 |

    2 | Joe | joe@gm.com | 3456 | =--MID($C2,3,1) | =--MID($C2,2,1) | =--MID($C2,1,1) | (Sample row)

    3 | Bill | bill@gm.com | 5643 | ? | ? | ? | (first autofill row after zapier places base data)


    I want each new row placed by Zapier to fill down the formulas in D, E, F
    Last edited by banyanman; 05-02-2016 at 07:18 PM.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,103

    Re: Auto Fill Formula into New Rows/Columns

    I suggest that you convert your data to a Table. If new data is appended at the end of the table, the table is automatically extended and the formulas are automatically copied down.

    Select the existing data including the headings and columns with formulas, go to Insert > Table, check "My table has headers" and hit OK.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    04-27-2016
    Location
    San Diego, CA
    MS-Off Ver
    14.6.1
    Posts
    6

    Re: Auto Fill Formula into New Rows/Columns

    I believe that what I am looking for are formulas that use arrays to do this. Tables don't play well with Zapier, especially in Google Sheets.

  4. #4
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,103

    Re: Auto Fill Formula into New Rows/Columns

    I'm not sure what you mean. If formulas use arrays they are still not magically filled down if data rows are added. The only two ways I know to do this are tables and macros.

    If you are using Google Sheets then we should move your thread to the Other Applications forum, since a pure Excel solution may not work for you.

  5. #5
    Registered User
    Join Date
    04-27-2016
    Location
    San Diego, CA
    MS-Off Ver
    14.6.1
    Posts
    6

    Re: Auto Fill Formula into New Rows/Columns

    I found this, but can't make it work for my use:
    =arrayformula( if( row(D:D)=1; "Whatever column D is called should be here";IF(ISBLANK(A:A),"",MONTH(A:A)) ))
    (this is labeled for another spreadsheet)

  6. #6
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,103

    Re: Auto Fill Formula into New Rows/Columns

    Note: The following reply would apply to Excel, but apparently the formula is perfectly valid in Google Sheets. I am leaving the reply intact to preserve the conversation but it is not what banyanman is looking for.

    That formula is a mess--where did you find that. First, there is no built-in function called ARRAYFORMULA. It is possible that the author meant to say this formula should be entered as an array formula, or possibly there is a UDF called ARRAYFORMULA. Second, the formula uses commas in one place and semicolons in another. In the U.S. configuration of Excel, only commas are used. Some other localizations use semicolons. But you can never mix them.

    And in spite of all that, even if this formula works it will not solve your problem. It can still not automatically appear in a row just because data is put there.

    The only other thing I can think of is for you to prepopulate formulas in D:F for the number of rows that is the maximum number of rows you could import from Zapier. And you would use formulas like these. Then those columns will appear blank until data is entered in column C.

    | =IF($C2="","",--MID($C2,3,1)) | =IF($C2="","",--MID($C2,2,1)) | =IF($C2="","",--MID($C2,1,1)) |
    Last edited by 6StringJazzer; 05-03-2016 at 09:27 AM. Reason: added blue text

  7. #7
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    365
    Posts
    18,103

    Re: Auto Fill Formula into New Rows/Columns

    I found the formula you referenced. Clearly you need a Google solution, not an Excel solution, so I am moving this thread. What I provided above may help but it looks like Google will autofill in situations where Excel will not, so you need a different solution.

  8. #8
    Registered User
    Join Date
    04-27-2016
    Location
    San Diego, CA
    MS-Off Ver
    14.6.1
    Posts
    6

    Re: Auto Fill Formula into New Rows/Columns [Google Sheets]

    Thanks all for your help! This solved it:
    =arrayformula( if( row(D:D)=1,"Presence",IF(ISBLANK(A:A),"",--MID(C:C,3,1)) ))

    Eric

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. auto fill rows based on number and formula
    By work4nutz in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-06-2013, 07:23 PM
  2. Dropping cell numbers in a formula as you auto-fill across columns
    By Nebs in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-23-2013, 12:48 AM
  3. [SOLVED] auto Deleting contents of certain columns when moving rows between sheets with one click
    By R.Sloan in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 04-18-2013, 04:57 AM
  4. Macro VB for Formula Fill Through Several Rows in Multiple Columns
    By sanjeevpandey in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-25-2013, 11:04 AM
  5. Custom Auto-Fill Question Similiar To Google
    By Peeekay in forum Excel General
    Replies: 7
    Last Post: 11-11-2010, 02:42 PM
  6. Auto-Fill From Rows To Columns
    By scottbrown_14 in forum Excel General
    Replies: 1
    Last Post: 06-08-2010, 12:38 PM
  7. [SOLVED] auto fill formula on new rows
    By RompStar in forum Excel General
    Replies: 4
    Last Post: 05-06-2005, 12:06 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1