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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

AlanConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.