I believe a good story can make up for weak gameplay because I am as entertained by the story aspects of the game as others just may be totally psyched on the gameplay and can forgive terrible story.
The example I can think of is Psychonauts. There may have been some hitches in the gameplay, but I barely found them to be distractions from everything else the game is storywise and all out (crazy-fun) ridiculousness.
I do not care for the opposite, however. I cannot think of any game I might have enjoyed slogging through for gameplay that had a weak story to it. I would tend to just finish it for the sake of... finishing. This is why I don't care too much for the two Halo games I've played through. Watching Emile's death play out just like Jazz' in the first Transformers movie might have more of an effect on me if I cared for who any of Noble Team is. I would've like to know why Jorge suddenly got mopey in space.
Then again, this might just be barking up the wrong genre. Then again, I can think of RPGs that lacked engaging story, so I'm thinking there could be FPSs out there with good stories to them.
Edit: I think there can be exceptions in an interesting story being unable to make up for borderline broken gameplay, however. The example I thought of is Rule of Rose, but this might be another matter altogether.