Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Dynamic range and Index Match

0

I have written these questions on the attached spreadsheet

Question 1  How do I write the correct formula for the above row 4

  Question 2  How can I make the named ranges dependent on the values in column A   so if I load new data into columns B and C (the new data may extend down additional rows)   With the new data - RANGE1 would expand addl rows if "BRONTON" was in more rows that is   curtenly shown - and so-on for the other ranges.                           Thanks  Carroll      
Answer
Discuss

Answers

0

For question number 2, is this what you are talking about...

=IF(B4="BRONTON","RANGE1",IF(B4="TREK","RANGE2",""))

If so, you just have to continue that pattern for as many options as you want. It's not ideal, but it will work.

Discuss

Discussion

Thanks for your help Don.  I think I need to create several dynamic ranges, since when I load new data, BRONTON may be in more rows(they will be contigious).  For example a new load of data the rows may change -- such as:
BRONTON  10 ROWS
TREK 2 ROWS
SPEC  12 ROWS
CERVELO  20 ROWS

So I need the ranges to automatically change.  Not sure how to do this.
Carroll

BTW the correct formula for question 1 is  =IFERROR(INDEX(RANGE1,MATCH(E$2,$C$4:$C$7,0)),"")
carroll (rep: 16) Apr 5, '19 at 8:35 pm
You could take a look at this tutorial and update the formulas to be dynamic: 
Dynamic Formulas that Update When you Add Data in Excel or this one: Dynamic Named Range in Excel or just turn the data into a Table (Insert tab > Table) and then reference the data columns there.
don (rep: 1989) Apr 9, '19 at 9:43 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login