+ Reply to Thread
Results 1 to 3 of 3

Google Sheets: Get formula to drag down automatically as new data is added

  1. #1
    Registered User
    Join Date
    11-03-2020
    Location
    Gloucestershire, England
    MS-Off Ver
    v16.31 Office 365
    Posts
    1

    Question Google Sheets: Get formula to drag down automatically as new data is added

    Hi,

    My company's IT person has got customer data automatically adding to the bottom of a Google Sheet.

    We then have an =importrange into another sheet where we will be adding to that data with formulas.

    So for example, we have a formula to work out how many days ago the customer was created. =DAYS(TODAY(),W2).

    The problem is, as more data gets added every day, the formula needs to get dragged down as the rows get added. We can't be doing this manually as we are setting up automated reporting dashboards.

    If we drag down extra rows below the data, the Julian date is displayed - which our reporting dashboard will treat as data and it'll therefore throw everything off.

    Does anybody know of a was to get the formula to go down as more data is added to the sheet?

    Thanks,
    Andrew

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Google Sheets: Get formula to drag down automatically as new data is added

    Use Excel Tables

    For Google Sheets, you need to pre-fill your entire column with formulas that look for the existence of data like so:

    =IF(W2="","",DAYS(TODAY(),W2))
    Last edited by Bernie Deitrick; 11-03-2020 at 10:48 AM.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Google Sheets: Get formula to drag down automatically as new data is added

    This is especially a case where it'd be useful to see a sample workbook. I understand you can't because it contains proprietary information, but you could create a copy with all letters other than X replace by X and all numerals other than 9 replace by 9.

    If you're using cells which are filled by an importrange function call, you should be able to use Google Sheets array formulas. I'll assume your source data generated by the importrange function call is in columns A to Z in a given worksheet, and row 1 contains field names/column labels. Ensure there's also column AA to AF in that worksheet. Enter the formula

    AB1: =arrayformula(max(row(A1:Z)*not(isblank(A1:Z))))-1

    and name cell AB1 NumRecs, for number of records. You could then use formulas like

    =arrayformula(days(today(),W2:INDEX(W:W,NumRecs)))

    in some column beginning, presumably, in row 2. As more records appear in the importrange results, NumRecs will increase, and formulas like the one immediately above will adapt and fill into NumRecs rows.
    Last edited by hrlngrv; 11-04-2020 at 08:49 PM. Reason: adjusted to OP example ranges

+ 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. Google Sheets: Merge dynamic ranges automatically (skipping rows without a visible data)?
    By rcurious in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 15
    Last Post: 06-21-2020, 09:27 AM
  2. Automatically import data from Google Sheets to Excel
    By samvince in forum Excel General
    Replies: 3
    Last Post: 05-12-2020, 03:53 PM
  3. RANK/automatically put my data in highest number order (google sheets)
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 2
    Last Post: 02-28-2020, 01:19 PM
  4. [SOLVED] Dashboard Automatically Updating with Data on Newly Added Sheets
    By magz108 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-06-2016, 10:20 AM
  5. [SOLVED] Can I make excel automatically drag-down a formula as you fill in data?
    By Yoshi64 in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 01-16-2014, 07:28 PM
  6. Automatically move a formula line down when new data is added
    By rlsublime in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-23-2011, 02:19 AM
  7. Automatically moving data rows to other sheets as new entires are added.
    By yehuoy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2011, 05:11 PM

Tags for this Thread

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