Google Sheets referencing another sheet every nth row

0
votes

In Google Sheets, I want to grab the text value from a sheet and put it every three rows in a separate sheet. I want there to be blank spaces in the rows in between.

For example, say I have a list of names on a sheet called "Customer Info" In Column A. On a separate sheet, I am keeping track of interactions with customers, and that data takes 3 rows. Every third row, I want to grab the name of the next customer from my sheet "Customer Info" and put it in column A of my sheet "Transactions."

I've been trying to find a way to use something like ='Customer Info'!A(x), and use a formula to get the value of x. I thought some variation of MOD would work.

2
google-sheets
share a copy of your sheet – player0

2 Answers

1
votes

Say Sheet2 is like:

enter image description here

In Sheet3 cell A1 enter 1 and in A2 enter:

=IF(MOD(ROW(),3)=1,1+MAX($A$1:A1),"")

and copy downwards. In B1 enter:

if(A1="","",index(Sheet2!A:A,A1))

and copy downwards:

enter image description here

You can change the number of empty rows by changing the arguments to MOD().

1
votes

try:

=INDEX(TRIM(FLATTEN(SPLIT(QUERY(INDIRECT("Customer Info!A2:A"&
 COUNTA(A2:A)+ROW(A2)-1)&"♀ ♀ ♀",,9^9), "♀"))))

enter image description here