CODEWITHSUNDEEP
excelvbacopy-pasteoffset
user3925803
user3925803

Reputation: 3289

Excel copy/paste formula with INDEX or OFFSET (relative reference)

Normally when you copy and paste formulas into different cells, column and row references are relative by default unless you add the '$' symbol, which makes them absolute.

But with INDEX and OFFSET, when I copy and paste, the column and row references seem to be absolute only. Is there a way to make them relative?

A have a named range and I want to find the sum of each row in that range. I'm using:

=SUM(INDEX(Q1UnitTotals,1,0))

which works fine, but when I copy and paste, it does not change the row number.

Upvotes: 1

Views: 1631

Answers (1)

chris neilsen
chris neilsen

Reputation: 53136

That's because the 1 is a value, not a range reference. To make it dynamic you need to make it a range reference. This can be done with ROW()

If the Sum formula is in row 1 use

=SUM(INDEX(Q1UnitTotals,ROW(),0))

If the Sum formula is not in row 1 use

=SUM(INDEX(Q1UnitTotals,ROW(A1),0))

Upvotes: 2

Related Questions