Dynamic named range changes with insert

I have a dynamic named range defined like this:

=Inmatning!$A$3:INDEX(Inmatning!$A:$A;MATCH(BigText;Inmatning!$A:$A))

My problem is when I want to insert a row in the range with this code

Rows("3:3").Insert Shift:=xlDown

the definition of the named range changes to

=Inmatning!$A$4:INDEX(Inmatning!$A:$A;MATCH(BigText;Inmatning!$A:$A))

I need the named range definition to NOT change. How can I solve this?

Thanks,

/peter
LVL 22
pivarAsked:
Who is Participating?
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Connect With a Mentor Microsoft MVP ExcelCommented:
Hello, you could use=Index(Inmatning!$A:$A,3):INDEX(Inmatning!$A:$A;MATCH(BigText;Inmatning!$A:$A))cheers, teylyn
0
 
jkpieterseCommented:
Just a remark on your MATCH function: You do know that -since you omitted the third argument- the match does not necessarily return exact matches?
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
@JKP,

in this case, the exact match is not required. The purpose of the last Index/Match is to return the last populated cell, hence BigText. Omitting the third argument will default it to 1, which is exactly what the formula should be.

I agree, though, that it's always better to state the argument than to omit it.

cheers, teylyn
0
 
pivarAuthor Commented:
jkpieterse:
I'm using this definition to find the last used row. BigText is =REPT("ö";50)
0
 
pivarAuthor Commented:
Thanks, that solved it.

/peter
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.