How to get the INDIRECT function and an R1C1 notation for entire column?

I had this question after viewing How to change this dynamic row named range to also become dynamic column?.

I found Neils answers very useful in the referenced link.    I do not know how to use the INDIRECT function and an R1C1 notation  for the entire column Reference for example this part COUNTA(Sheet1!$A:$A)

can u help plz?

=INDIRECT("Sheet1!R2C" & MATCH("Order ID",Sheet1!$1:$1,FALSE) & ":R" & COUNTA(Sheet1!$A:$A) &"C" & MATCH("Order ID",Sheet1!$1:$1,FALSE),FALSE)
LVL 6
FloraAsked:
Who is Participating?
 
AlanConnect With a Mentor ConsultantCommented:
Hi,

Try this:

COUNTA(Sheet1!C1)

Open in new window


Note that you need to be careful not to get confused - it *looks* like it could be A1 notation, but as long as you are using R1C1 notation, the above is the equivalent of:

COUNTA(Sheet1!$A:$A)


Alan.
1
 
Rob HensonFinance AnalystCommented:
Have you tried the OFFSET function as I suggested after you closed the previous question?

If you upload a sample I can put it in place.
1
 
FloraAuthor Commented:
Thank you so much Alan
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
FloraAuthor Commented:
Thanks Robert.  what i was looking for was the solution with INDIRECT and R1C! notation.  Neils and Alan's solutions worked for me.

thanks alot for your help as usual.
0
 
Rob HensonFinance AnalystCommented:
Why use INDIRECT and a text string when OFFSET does exactly the same but much neater?
0
 
FloraAuthor Commented:
Thanks Robert.

the reason i could not make your Offset solution work is,    please see attached file.   because it uses the column A for counting non blank cells.

now if my Order ID title gets moved to column E.  your formula works. but it has a problem.  when i have less rows with data in column A. then the named range leaves out cells.

EE.png
=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(Sheet1!$A:$A)-1,1)

i posted a new question for this https://www.experts-exchange.com/questions/29068595/Folow-up-question-on-dynamic-range-from-answer-given-by-Robert-Henson.html
EE.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.