Go Premium for a chance to win a PS4. Enter to Win

x
• Status: Solved
• Priority: Low
• Security: Public
• Views: 35

# 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)
0
Flora
• 3
• 2
1 Solution

ConsultantCommented:
Hi,

Try this:

``````COUNTA(Sheet1!C1)
``````

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

IT & Database AssistantCommented:
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

Author Commented:
Thank you so much Alan
0

Author 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

IT & Database AssistantCommented:
Why use INDIRECT and a text string when OFFSET does exactly the same but much neater?
0

Author 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 https://www.experts-exchange.com/questions/29068595/Folow-up-question-on-dynamic-range-from-answer-given-by-Robert-Henson.html
EE.xlsx
0