I'm using Google Sheets. Every week I add a column with new data, and I need the data from the latest column copied into another sheet. The formula needs to pull the cell of the last column in the row.

I've googled left and right and tried and tried.

The "best" formula that I have tried is:


But I get:

error: Reference out of range

I've tried =index(2:2, 5), as a test, which should bring the data from row 2 column 5, no luck.

  • Are you building a copy of the source sheet? or a new sheet for each new Column you add? Do you have a sheet or an example that you could show.
    – CamSyl
    Jan 25, 2015 at 3:49

2 Answers 2


Provided that last column is populated, then something like:


assuming a row starts in ColumnB and ends in ColumnZ.


I figured out the issue.

From Google:

Sample Usage

INDEX(A1:C20, 5, 1)


INDEX(reference, [row], [column])

reference - The array of cells to be offset into.

row - [OPTIONAL - 1 by default] - The number of offset rows.

column - [OPTIONAL - 0 by default] - The number of offset columns.

I needed to offset by column, not by row. So my original formula


counta(2:2) is supposed to bring me the row # based on the 'counta' of row 2:2, ie. the last row. But I actually need it to bring me the column.

This is the working formula:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.