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)
Who is Participating?
AlanConnect With a Mentor ConsultantCommented:

Try this:


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:


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.
FloraAuthor Commented:
Thank you so much Alan
Never miss a deadline with

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

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.
Rob HensonFinance AnalystCommented:
Why use INDIRECT and a text string when OFFSET does exactly the same but much neater?
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.

=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
All Courses

From novice to tech pro — start learning today.