How to copy every 3 rows down (2 row cell with formula and 1 blank cells) for the next formula just go for 1 cells instead of go for 3 cells

4
votes

I'm trying to copy 2 rows of formula and 1 blank row to fill down my required row, but when I'm trying to do that the formula in next row jump 3 cells, I want it so the next formula I paste just the next cell

Expected Result

Link to sheet: https://docs.google.com/spreadsheets/d/1VAhls2rzaAJZiiRA-Z0Q2MusfCa8v1YoD1n2NxaCwzQ/edit?usp=sharing

sorry for my very terrible way of describing my problem, because I'm rarely using English for coding Q&A

2
google-sheetsgoogle-sheets-formula

2 Answers

1
votes

all you need is this I guess:

=ARRAYFORMULA(SUBSTITUTE(TEXT(REGEXREPLACE(TO_TEXT(TRANSPOSE(
 SPLIT(TEXTJOIN( , 1, TRANSPOSE(QUERY(TRANSPOSE(QUERY(
 {IF('2019'!D2:D=0, "♥", '2019'!D2:D*0.0001), 
  IF('2019'!G2:G=0, "♥", '2019'!G2:G*0.0001)},
 "select Col1,Col2,'♦ ♠' label '♦ ♠'''")), , 500000))), " "))), 
 "♦|♠|♥", ""), "0.0000"), "0.0000", ""))

0

if you need to output real number then wrap it after ARRAYFORMULA( into VALUE()

demo spreadsheet

0
votes

The automatic alteration of formulas in Sheets when moving, copy-pasting, or drag-copying is not very sophisticated. It's also not configurable.

There may be more elegant workarounds, but I think the simplest thing to do is stop relying on it. If Sheets is bad at changing your formula, try a different formula that doesn't need to be changed in that staggered way. For example, A5 and A6 could be: 

=if(INDIRECT("2019!D"&ROW()+1)=0,"",INDIRECT("2019!D"&ROW()+1))
=if(INDIRECT("2019!D"&ROW()  )=0,"",INDIRECT("2019!G"&ROW()  ))

This works because in A5 we want to refer to sheet 2019 row 6, so we use an INDIRECT reference to A5's row number (5) plus 1 (6).
When we're in A6, we instead want 2019 D6 and G6, so there's no adding 1.

If you can read my pseudo-formulas, your example sheet seems to follow the pattern:
  if 2019!D–next row = 0, output nothing, otherwise 2019!D–next row
  if 2019!D–this row = 0, output nothing, otherwise 2019!G–this row
  <blank cell>

Using the above formulas, if you select those three cells (formula, formula, blank), then drag-copying them keeps them working the way you wanted.