google sheets – Merged cells autofilling

I have a sheet with merged cells in a column. Merged cells are of different sizes (not the same number of rows). I want to autofill this column, for example by typing “1” in the first one, then extend it down and get 2, 3, 4… But if tried that, the size of the first merged cell is also extended and all my cells get the same size… Which is not what I expect.

I also tried using a formula “=max(A$1:A1)+1” in the cell A2 and then using Ctrl+Enter to copy it in all the cells but the same issue arise : my merged cells get resized…

So my question is : is there an easy way to autofill merged cells of different size in Google Sheet ?

microsoft excel – Count first cells in a range of rows, which are higher than 0 and meet criteria

For each column, I would like to count the amount of cells, which are higher than 0, appear for the first time in the row, and meet a criteria in one of the columns.

In this example, I would like to count the number of new customers for each months, the number of new customers in a specific region, and the number of customers in a specific industry.


Can someone help?

google sheets query – Macro to copy and paste values from multiple cells as text into a Note

I’m new here and struggling a bit with the javascript in macros. I just need a steer as to how to get a macro to copy from a range relative to an active cell.

I’m a showcaller, that is, during live events I tell technicians when to play videos or music or to change lights etc.

I use a Google Sheet for my running order and each line contains the various cues – actions that technicians must take, like ‘play Video 1’ – separated into columns for the different departments: Lights, Sound, Video and so on.

I want to gather the cues for different departments as text in a Note in a single cell. I can then edit the text to write my standbys – the warnings of upcoming cues – and read them from the Note during the event.

So I am trying to make a macro to take the contents of the cells in a relative range and paste them together as text in a Note in a single cell.

I’ve tried recording a macro and using cut-and-paste to select a range and put it into a Note on the active cell. However this only placed the contents of the clipboard into the Note as text – it didn’t do the cut-and-paste as part of the recorded actions in the macro.

I’m happy to do the work to write it – can anyone help me, please, with the correct way to have the macro copy and paste from a relative range. Thank you.

How do I populate cells (with formulas) from one google sheet into another while using one as a search key for the other? If C=This, State D

(!(I’d like to populate Columns C and D in an entirely new spreadsheet, where Column D still autopopulates the cells just as it does in the original/first sheet (as it does in the photo attached inside this link)

I’ve tried the formula =IMPORTRANGE(“1jD9MgFRnIi-44GaqsS5XAdhcV-Vg36eXnAXzkFh5xnM”,”‘Form Responses 1’!C2:C”) in the new spreadsheet and it populates C, however, I only want it to show D IF/WHEN someone types their Username in C… If that makes sense.

P.s. I also only want two rows and two cells in the “new spreadsheet”. One Column for the search box where they type their username, and the other for the content in D that corresponds to that username in C)1)1

google sheets – How do I get a formula copied to all the cells below it in the entire column (e.g. 900’000 cells) instead of manually dragging it down to eternity?

If I have a formula, =IF(MOD(ROW(),1)=0,A2+1,A2) in Cell A2, how do I get it copied to all the cells below in the entire column (e.g. 900’000 cells) instead of manually dragging it down to eternity?

Also would like for this formula to be applied to the entire column, =ArrayFormula(IFERROR(VLOOKUP(B2:B,$A$2:$D,3,0))) , from cell E2. – Note that the new cell has been added, but does not match the style of the table. How can the style of cells be adjusted to match other cells?

In, do the following:

  1. In "Search forms" enter "Table" use the ENTER key to search.
  2. Choose the first array of green colors by selecting, dragging and
    fall on canvas.
  3. Double-click on the table to obtain the "Table Object in the layout text" menu.
  4. Click once on a cell at the bottom of the table.
  5. Double-click the "Insert Row After" icon.

enter description of image here


How can I increase the number of Google sheet cells by adding 20 to a form

I would use "+" or "-" to reference a cell, for example

= i (10 + 23) = i33.

To avoid having to manually increment the cell reference.

Any help is welcome ?

Use countblank to count the number of empty merged cells in Google Sheets

I am trying to get the number of empty merged cells in a range, say for example the range of 6 cells A1:F1. If the cells weren't merged, I could just use =COUNTBLANK(A1:F1), but let's say A1:C1 are merged and D1:F1 are also merged. If I have a value in A1:C1, =COUNTBLANK(A1:F1) will display 5 instead of desired 1.

I am working in a Google Sheets spreadsheet which has been heavily formatted by the designer to be the output of other data in the spreadsheet. The design uses a lot of merged cells and, unfortunately, removing them is not an option.

One way to accomplish this?

Google sheets sum of colored cells TypeError: Unable to read the "pop" property of null

I use this gentleman script:

To summarize the value of the colored cells only, but I get an error message saying "Error
TypeError: Unable to read & # 39; pop & # 39; property null (line 15). "

Here is the script he created that I am trying to use:

    * @param {range} sumRange Range to be evaluated
    * @return {number}
    * @customfunction

    function sumColoredCells(sumRange,colorRef) {
      var activeRange = SpreadsheetApp.getActiveRange();
      var activeSheet = activeRange.getSheet();
      var formula = activeRange.getFormula().toString();
      formula = formula.replace(new RegExp(';','g'),',');

      var rangeA1Notation = formula.match(/((.*),/).pop();
      var range = activeSheet.getRange(rangeA1Notation);
      var bg = range.getBackgrounds();
      var values = range.getValues();

      var colorCellA1Notation = formula.match(/,(.*))/).pop();
      var colorCell = activeSheet.getRange(colorCellA1Notation);
      var color = colorCell.getBackground();

      var total = 0;

      for(var i=0;i

Maybe Google has changed something in the environment that completely breaks this script? I see that it was written a few years ago ...

I also ran into someone with a similar problem and their solution was to replace lines 15 and 20 respectively:

var rangeA1Notation = formula.match(/sumColoredCells(((^,)*),/).pop();


var colorCellA1Notation = formula.match(/,(w{2,}))/).pop();

But that doesn't change the error.

How can I divide a text cell with the date and time into two separate cells?

I am trying to format a .csv to import it somewhere, and I have a column of cells with plain text formatted "Monday 02 11:11:52" – I would like to divide them into two columns formatted with the date (Monday 02) and the time (23:11)