Hello,

the formula provided above will create a range that starts in A1 and spans all used columns.

If you only need one column and you know the column letter, say column C, you could use

=OFFSET(Sheet1!$c$1,0,0,COUNTA(Sheet1!$c:$c),1)

If column C may contain blanks, but column A always has data in each row, you may want to use column A for the vertical sizing of the column, like this:

=OFFSET(Sheet1!$c$1,0,0,COUNTA(Sheet1!$a:$a),1)

In words that formula means: Start in cell C1, go 0 cells down, go 0 cells to the right. Make the selection as high as there are populated cells in column A and make the selection one column wide.

Note that with the CountA function the returned range will be too short if there are blank cells in column A. There are other ways to determine the last used cell in column A (or C). You should be fine if there are no blanks in the data.

cheers, teylyn

the formula provided above will create a range that starts in A1 and spans all used columns.

If you only need one column and you know the column letter, say column C, you could use

=OFFSET(Sheet1!$c$1,0,0,CO

If column C may contain blanks, but column A always has data in each row, you may want to use column A for the vertical sizing of the column, like this:

=OFFSET(Sheet1!$c$1,0,0,CO

In words that formula means: Start in cell C1, go 0 cells down, go 0 cells to the right. Make the selection as high as there are populated cells in column A and make the selection one column wide.

Note that with the CountA function the returned range will be too short if there are blank cells in column A. There are other ways to determine the last used cell in column A (or C). You should be fine if there are no blanks in the data.

cheers, teylyn