Google Sheets: When copying formula into the next row, it selects the next column as the reference

[p_s]

Hi,

Is there a way for you to have the formula reference the next column instead of the next row when you copy down a row? For example: consider cells in the last column. Assume you copy and paste the formula in E1 into E2 & E3.

For examples

Chances	Win Total	Amount	Number of tries	SUM(A2:A6)
3	45	123129	Number of Wins	SUM(B2:B6)
4	23	234234	Total Amount	SUM(C2:C6)
5	435	23525254566
6	324	25252412
7	234	2522242

 

[jasonb75]

In excel you could use something like

=SUM(INDEX($A$2:$C$6,0,ROWS($E$1:$E1)))

which should also work in google sheets.

 

[p_s]

Hi Jason

Thanks for your help:

What if the formula is more complicated? I tried this on my sheet, it wasn't for a sum, but I figured the concept would be the same, but it just gave blanks. Chances are high that I didn't understand the theory correctly.

Cell ref (B55): Person	Cell Ref (G55) List of Items
(B56) #1	(G56) =iferror(if($B56="","",join(", ",FILTER($F$10:$F$51&" "&$B$10:$B$51,$F$10:$F$51>0))))
(B57) #2	(G57) =iferror(if($B57="","",join(", ",FILTER($G$10:$G$51&" "&$B$10:$B$51,$G$10:$G$51>0))))
(B58) #3	(G58) =iferror(if($B58="","",join(", ",FILTER($H$10:$H$51&" "&$B$10:$B$51,$H$10:$H$51>0))))

What if there are merged cells/gaps between formulae? This worked for C1, but when I tried to expand it, the $C1:$1, changed to $C1:8 and if I changed it back to $C1:$1 and the A8 from Row($A$3:$A8) to A4, it would return the same value or a completely different value from what it should have. But also having to change each formula just makes it more complicated, I might as well have copy pasted the formula it already has and then edited it.

Example:

	First Name	Jane	John	FName
	Last Name	Doe	Doe	LName
"=Worksheet!C1"	Main
	Criteria 1
	Criteria 2
	Criteria 3
	Criteria 4
	Criteria 5
	Criteria 6
"=Worksheet!D1"	Main
	C1
	C2
	C3
	C4
	C5
	C6
"=Worksheet!E1"	Main
	C1
	C2
	C3
	C4
	C5
	C6

 

[jasonb75]

If C1:C7 is merged then it is still counted as 7 rows by the formula. You can use COUNTA instead of ROWS which will only see each merged range as a single item.
From the formulas that you show, best guess would be

=iferror(if($B56="","",join(", ",FILTER(INDEX($F$10:$F=H$51,0,COUNTA($C$1:$C1))&" "&$B$10:$B$51,INDEX($F$10:$F=H$51,0,COUNTA($C$1:$C1))>0))))

 

[p_s]

ERROR: "FILTER has mismatched range sizes. Expected row count: 42, column count: 1. Actual row count: 73, column count: 1."

ERROR: "Array arguments to CONCAT are of different size."

Editing Cells G56 and down I used both ROWS and COUNTA in different cells to see which would work, but neither did, so I thought I'd share my sheet to make it easier for you.

 

[p_s]

Nevermind, I figured it out:

For my formula for the 1st part I used:

=(if($B56="","",join(", ",FILTER(INDEX($F$10:$P$51,0,ROWS($G$56:$G56))&" "&$B$10:$B$51,INDEX($F$10:$P$51,0,ROWS($G$56:$G56))>0))))

When I'd used this earlier it hadn't worked. IDK why.

 

[jasonb75]

With the error you describe it sounds like you had a typo in one of the ranges, based on the error information it would suggest that you perhaps had $82 instead of $51 in one of the index ranges, although that does seem like an extremely unlikely typo.

 

[p_s]

With the error you describe it sounds like you had a typo in one of the ranges, based on the error information it would suggest that you perhaps had $82 instead of $51 in one of the index ranges, although that does seem like an extremely unlikely typo.

I don't actually know what I did to fix it. It wasn't a typo. I have copied and pasted the formula as you had typed it and then just changed cell and range references to fit my sheet. I had the same problem with a query function I had typed. I didn't make any changed, just copy and pasted formulas I had checked worked before had, but for some reason it didn't work, and then just as I was typing up a post on this forum about it, it magically worked.