Used as a sarcastic comment for someone who stated the obvious, using a reference to the famous fiction detective Sherlock Holmes, it can be understood as "No way, you are Sherlock Holmes!" which can actually mean "Of course, you dumbass!".
When you reply to spmebody who has just stated the very obvious ''no shit,sherlock' to imply that yep, everyone knows the answer already and there pronouncement has come as no surprise.