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

  • Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 35
  • Last Modified:

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

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 HensonIT & 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.
FloraAuthor Commented:
Thank you so much Alan
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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 HensonIT & Database AssistantCommented:
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 https://www.experts-exchange.com/questions/29068595/Folow-up-question-on-dynamic-range-from-answer-given-by-Robert-Henson.html

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now