When you have eliminated the impossible, whatever remains, however improbable, must be the truth.
I've heard this often quoted as a basis for problem solving. But it is very wrong. Given this view it should be no surprise that Doyle was a spiritualist and an easy target for scams.
There are two basic fallacies. One is that you've truly eliminated other choices as being impossible and secondly that you've enumerated all of the possibilities. In practice, if all you have left is something that is very improbable, then you should, of course, give it consideration. But you must also reexamine not only the other possibilities but your very premises.
It's not only possible that you may have mistakenly eliminated other possibilities, you may be asking the wrong question.
Misdirection is a standard trick of magicians that is used to play upon this and lead the viewer to consider the wrong set of possibilities. A classic scam is to send out a set of predictions to a large audience. For example, if you predict the winners in three baseball games, there are eight possible sets of outcomes. If you send a different set to each person, then one of the eight will see a perfect set of predictions. To that person, there is no possibility of fraud since you obviously couldn't know the outcomes in advance.
Sherlock Holmes makes great fiction. But in the real world, the ability to posit a long string of events perfectly is more likely to lead one to false confidence than a correct conclusion.