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 '15 at 3:49

Provided that last column is populated, then something like:


assuming a row starts in ColumnB and ends in ColumnZ.

| improve this answer | |

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:

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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