I have a spreadsheet I'm trying to keep fairly clean. So I'm using a second sheet to do my math on, which is based on the location of info on the main sheet. The end result is that I have a column where:

G2 contains =SUM('Sheet2'!A2,'Sheet2'!C2)

G3 contains =SUM('Sheet2'!A3,'Sheet2'!C3)

etc. For about 500 rows. The problem is that if I enable a filter and change the order of the rows or even manually drag a row elsewhere, the contents of every cell remains the same. I want them to update, so that if G2 suddenly becomes G69, it automatically changes to say =SUM('Sheet2'!A69,'Sheet2'!C69)

I suspect the fact that I'm referencing another sheet but still want to update the row number automatically may make it even harder? But is there a way to do this? I tried =SUM('Sheet2'!$A2,'Sheet2'!$C2) but that didn't do the trick.

CodePudding user response：

use in G2 and drag down:

=SUM(INDIRECT("Sheet2!A"&ROW()), INDIRECT("Sheet2!C"ROW()))

CodePudding user response：

Use INDEX and ROW:

'Sheet2'!A2

will be

INDEX('Sheet2'!A:A,ROW())

Note: Traditional usage of INDIRECT in these cases are better avoided as INDIRECT is known to interfere with built in spreadsheet optimizations. Convertion of string to range is also avoided.INDEX is also better suited for such range manipulations, because it supports arrays better(eg: =INDEX(A1:INDEX(A:A,5))) and always acts like a array formula.