Populate columns from another sheet if condition met - Dynamically

[sanket87]

Hi Guys,

I have a problem in excel for which I need your help . Basically I want to populate few columns from another sheet if condition met . For example -

Sheet 1 - Has 3 columns - Defect id, status, description

Sheet 2 - Will have 4 columns - Defect id, status, description and Analysis

Now condition part is - a) Sheet 2 should have only those columns where status is complete .
b) For each row I will write analysis(4th column) .

Till here problem seems simple and I am able to achieve if I have to do this only once .

Tricky part is - Sheet 1 can change any time and rows can come in any order . Basically I am using RERESH function (I am taking these rows from another excel sheet) .

My problem - My problem is first 3 columns of sheet 2 are fine as those are getting populated from vlookup or IF functions but what about 4th column (Analysis) ? As this columns I am writing . Next day when I do refresh all in sheet 1 , shuffling happening . As Analysis column is static .

I tried my best to explain but please ask me any question in case of doubts . I am very new to excel .

Thanks !!