Dynamic Named Range + Index/Match Returning Circular Reference

Book18.xlsx

Hi Everyone! I uploaded a picture to show an extremely simplified version of my file.

The overall issue is that I'm trying to put this formula: =index(division,match(b2,department,0)) into cells A2:A6.

I am trying to create a dynamic range that starts at A7 - the last row will be different each time I run this report. This also includes my 2nd named range which starts at B7.

I've tried every way possible to mess with the dynamic range so that my index/match doesn't think i'm referencing the entire A:A range, but it keeps returning a circular reference.

The named range formula i'm using is this:
Division: =OFFSET(Sheet1!$A$7,0,0,COUNTA(Sheet1!$A:$A)-1,1)
Dept: =OFFSET(Sheet1!$B$7,0,0,COUNTA(Sheet1!$B:$B)-1,1)

I have also tried this (also didn't work)
=$A$7:INDEX('Source Data'!$A:$A,COUNTA('Source Data'!$A:$A))

Any ideas how I can accomplish what I need? THANK YOU!

Please post a sample workbook, not a picture. A picture is next to useless.

Sorry about my next to uselessness!

Not to worry. See the attached updated example. Not entirely sure what it is you are trying to do but this works.

Regards, TMS