Is it possible to refer to a whole column in Google Spreadsheets using R1C1 format?

Question

In Google Spreadsheets I often use A1 notation to refer to a whole column:

A1:A

For reasons I explain below, I'd like to use R1C1 format, to refer to a whole column, something like:

R1C1:C1

It's not possible to use A1:A in Excel, so as R1C1 notation originated from Excel, it seems unlikely that its possible to do this in R1C1 notation. If it is, it'd make my life easier.

Background: I have a spreadsheet visits, which pulls information from another spreadsheet data, which has a dynamically changing format.

To get around this, I use a formula to generate an address in a cell, based on the current layout of data. I can then reference this address cell in other formulae using indirect. And it's much easier to generate an address using R1C1 than A1.

Answer 1

Short answer

Yes, it's possible to refer a whole column using R1C1 notation: Use INDIRECT("C1",FALSE)

Explanation

Google Sheets doesn't include a feature to change the reference notation from A1 to R1C1 but the last could be used with the INDIRECT built-in function.

Example

In a sheet having 1000 rows, the following formula will return 1000

=ROWS(INDIRECT("C1",FALSE)) 

Answer 2

A1 notation is supported now. To center column A:

SpreadsheetApp.getActiveSheet().getRange("A1:A").setHorizontalAlignment("center");

Answer 3

No, but you can work around it, by knowing which row is the last row with data in a column, using the COUNTA function.

INDIRECT("A1:A") could be written something like INDIRECT("R1C1:R" & COUNTA(R1C1:R1000C1) & "C1")

You need to determine a sensible 'max rows' value. I've used 1000 in the example, so this formula should work in a spreadsheet with up to 1000 rows.

Look up COUNTA in the Google Spreadsheets function list.

Answer 4

Yes it is possible:

A1 Notation: A:A is an absolute reference to Column A whereas R1C1 Notation with square brackets is a relative reference, so you count from the current cell.

Example: If current cell is D1, then as column A is 3 columns to the left you can refer to it as C[-3]:C[-3].

Answer 5

The acceptable format varies depending on the method being used. In this one, for example, the description allows for R1C1 notation, but it's unclear (to me at least) whether the input or output is R1C1 notation.

I recommend adopting the use of getRange(row, column, numRows, numColumns) instead (Official doc here). Based on the given details of your intended use, this would suit your purposes quite well. I frequently use a variable for the 'row' parameter to retrieve a whole range of values in a loop. e.g., getRange(row, 2, 1, 7) where row = 3 would return the range C2:C8. Then, simply increment 'row' to return the next range D2:D8.

Answer 6

YES...refer to whole column as C:C... rather than range as C15:C25 ...