Search code examples
stringdategoogle-sheetsdragarray-formulas

Disable auto increment for a specific column

I have a Google Spreadsheets document with two columns: first a date column and then a text column (there are more columns in reality). The content of the text column ends with a number. If I select a line and drag it down to fill the next lines automatically, both values increments. If I hold down the Ctrl key, no value increments.

Is there a way to increment the date column but not the text column?

Solution

  • Google Sheets is not so versatile to possess the option to "disable auto increment for a specific column", but there are always workarounds/compromises...

    • drag each column separately
    • don't drag and use ArrayFormula to auto-populate cells on a go

    for example dates:

    =ARRAYFORMULA(TEXT(ROW(INDIRECT(DATE(2019; 10; 30)&":"&TODAY()+1)); "dd.mm.yyyy"))

    will populate dates between the given date (30.10.2019) and tomorrow's day. each day will be added a row with a date.

    to repeat same text:

    =ARRAYFORMULA("Test 1"&SUBSTITUTE(ROW(INDIRECT("E3:E"&COUNTA(E3:E)+2))^0; 1; ))

    will repeat given text as many times as are dates in E column

    0