This is a tricky one to explain,

The formula I am trying to use does look overly complicated as I'm sure people will say, but due to layout of the results I receive and how I wish to move them around this is the best method. The formula that isn't working is;

=(IF((OFFSET(INDIRECT("sheet2!D"&$A8),(Q$6),23,1,1))="Non-compliant",(OFFSET(INDIRECT("Sheet3!D"&$A8),(Q$6),35,1,1),(OFFSET(INDIRECT("sheet2!D"&$A8),(Q$6),23,1,1)))))

(IF((OFFSET(INDIRECT("sheet2!D"&$A8),(Q$6),23,1,1))="Non-compliant" to be the logical test

(OFFSET(INDIRECT("Sheet3!D"&$A8),(Q$6),35,1,1) for value if true

(OFFSET(INDIRECT("sheet2!D"&$A8),(Q$6),23,1,1))))) for the value if false


But for some reason my excel doesnt read the last part of the formula as [value if false], but keeps it in the [value if true bracket].


How do I resolve this?