0

I am trying to create dynamic ranges to be used for excel charts. I have the following range: =OFFSET(O7,0,0,1,COUNTA($O$5:$X$5))

cell o7 is expected to change. I have created the following formula to find the starting cell =CELL("ADDRESS",INDEX(N6:N14,MATCH("AC",N6:N14,0))) as result, it gives $n$7

however, when I tried to blend the second one in the first one, I receive an error.

My question is, starting from the first range (offset), how can I make the cell o7 variable possibly using an index formula?

Than you

Improve this question
3
  • 3
    How about: =INDEX(O6:X14,MATCH("AC",N6:N14,0),SEQUENCE(,COUNTA($O$5:$X$5))? Commented Dec 12, 2024 at 19:04
  • It works!!! THANK YOU SCOTT, I spent at least 1 hr on this before giving up. May I ask you to explain in few words how it works? I understand the index and match, however I am not familiar with the sequence. Thanks again for your much appreciated help Commented Dec 12, 2024 at 21:09
  • All the Sequence is doing is creating an array of 1,2,3,... to the number of columns that have a value in it. If O through X always has values and you always want to return all the columns then just put 0 in place of the whole SEQUENCE() function. The third criterion of INDEX is the column(s). 0 returns them all, while an array of numbers will return those columns. Commented Dec 12, 2024 at 22:08
Related questions
0
dynamic named range using offset/counta but with a twist
1
Excel Dynamic Chart using named range that can be empty
766
How to use Regular Expressions (Regex) in Microsoft Excel both in-cell and loops
Load 3 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.