12

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.

  • "As it's not possible to use A1:A in excel" - do you mean "As it's not possible to use R1C1 in excel" ? – Vidar S. Ramdal Oct 18 '12 at 12:22
  • No, I meant that the range A1:A only works in Google Docs - with excel you have to specify both row and column for the end of a range. – s6mike Oct 18 '12 at 18:21
  • 5
    I can't read R1C1 notation without wondering about a R2D2 one. – Sony Santos Jul 13 '15 at 16:39
2

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)) 
  • 2
    Genius! Thanks :) Now almost 4 years have passed since I asked the question, now I just need to remember what I was doing when I asked it! – s6mike Sep 3 '16 at 11:05
6

A1 notation is supported now. To center column A:

SpreadsheetApp.getActiveSheet().getRange("A1:A").setHorizontalAlignment("center");
  • 1
    A1 notation has always been supported, but my questions asks whether it's possible to to use R1C1 notation to refer to a whole column in Google Spreadsheets. – s6mike Nov 25 '15 at 22:56
2

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.

  • Now it's possible (see my answer. Maybe this was introduced on the new Google Sheets. – Rubén Sep 2 '16 at 22:08
  • Ah, that's great! – Vidar S. Ramdal Sep 3 '16 at 7:34
-1

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.

  • 1
    You are not answering the question of how to refer to the whole column, instead of a specified number of routes in that column. Using getRange repeatedly in a loop is not recommended unless necessary, it leads to poor performance and quota issues. – user79865 Jul 27 '16 at 21:25
  • 1
    As for what you found unclear in getRange method: its output is a range object, not a string, so there is no question of notation for it. The notation is specified for input, which is a string. – user79865 Jul 27 '16 at 21:27

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.