Dynamic fourmula

Hi two things

1. I have Pivot table and I am creating a colum at the end of it in a seprate cell and I am asking it to look at the value in b3 if it =failed then display completed late, I can get the fourmula to work but it does auto complete auto fit cause when change the site name everytiing else shrinks apart from the end colum that i have called reason. can this be made dynamic.

2, I have source data on 1 sheet and on blank sheet I am trying to do the following

if reactive data colum test pass fail = failed, then display worder number is coloum A

Any ideas