troubleshooting Question

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

Avatar of Flora Edwards
Flora EdwardsFlag for Sweden asked on
Microsoft ExcelMicrosoft Office
4 Comments1 Solution165 ViewsLast Modified:
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
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 4 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros