asked on

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)

ASKER CERTIFIED SOLUTION

Alan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob Henson

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.

Flora Edwards

ASKER

Thank you so much Alan

Flora Edwards

ASKER

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 Henson

Why use INDIRECT and a text string when OFFSET does exactly the same but much neater?

Flora Edwards

ASKER

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