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: 1665) 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