Google Sheets – How to Refer to a Whole Column Using R1C1 Format

google sheets

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.

Best Answer

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))

Related Solutions

Google-sheets – Updating data validation for a group of cells

I'm not 100% sure what the issue is, but if it is a question of how to change the range of options in data validation:

When using Data Validation, the drop downs will update automatically if you change the source-list of values that appear.

The only time you will need to re-configure the validation is if the range of the possible selections changes, or you start using a different range/sheet to validate the input cells.

Example:

options = [1, 2, 3, 4, 5]

Drop down shows:

[1, 2, 3, 4, 5]

If you want to add a value (like 6), you will need to re-do the validation because this will change the range of the options.

Google-sheets – How to make a simple two column data chart in Google Spreadsheets

Maybe this has been fixed in New Google Sheets because it works for me (indeed, it is VERY simple):

WA47150 example

Select A1:B3, Insert, Chart..., click Line chart, Insert.

Seems happy to work with DMY format and, as it happens, text for dates (since happening to be 4 months apart the x-axis spacing to be equal is about correct anyway).