Take the 2-minute tour ×
Web Applications Stack Exchange is a question and answer site for power users of web applications. It's 100% free, no registration required.

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 go generate an address using R1C1 than A1.

share|improve this question
    
"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
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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