Hey Thanks Pete,

So is there a way to reformulate that allows me to copy the formula from the example sheet and paste to Sheet1 so it will work and not throw the #REF! error?

Its just that my work template workbook, the data filtering table I made a macro for, will copy the formula and then paste it on the filtered table, while all the other data that is copied will be pasted as values only. So when I am looking at the filtered table and I need to look at a owner in more detail, I can click on the hyperlink to bring me to the sheet and owner name on the specified row within the sheet.