Folow up question on dynamic range from answer given by Robert Henson

Flora Edwards
Flora Edwards used Ask the Experts™
on
I had this question after viewing How to get the INDIRECT function and an R1C1 notation for entire column?.


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.  the formula below works. but it has a problem.  when i have less rows with data in column A. then the named range leaves out cells.  i need help with making the column used in COUNTA(Sheet1!$A:$A) also dynamic.

EE.png=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(Sheet1!$A:$A)-1,1)
EE.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016
Commented:
Hi,

pls try
=OFFSET(Sheet1!$A$2,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1,COUNTA(OFFSET(Sheet1!$A:$A,0,MATCH("ORDER ID",Sheet1!$1:$1,0)-1))-1,1)

Open in new window

Regards
Flora EdwardsMedicine

Author

Commented:
Thank you Rgonzo1971  it worked
Flora EdwardsMedicine

Author

Commented:
i also have a follow up question on VBA posting now.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial