The value I want in cell A2 of sheet 5 is 3
Since that value, per your example, comes from sheet3!A2, what is the criteria that this cell value should be used rather than, say, sheet1!A2, which = 5.

You need to provide a more explicit explanation and the criteria/logic for deciding which sheet/cell gets priority.