Someone who believes they always have the answers, a know-it-all. Usually used to insult that person when they make a stupid insight or just repeat what has already been said. Sherlock being a reference to the literary detective sherlock Holmes.
"We should fix this tire."
"Well, if I were you I'd fix the tire."
"NO SHIT SHERLOCK!"