It is a real issue. You can get sweet cider, or carbonated cider, but both....is tricky. The best way is to keg and force carbonate, naturally. But not everyone has the equipment to do that. Another way is to sweeten to taste with whatever preservative-free sweetener you wish, then add a bit more and allow to bottle carb. Fill a plastic bottle with the cider, and when it gets hard you immerse the bottles in a bath of 160*F water for ten minutes; this kills the yeast. This is a bit labor-intensive and some report that it changes the flavor a bit, but it undeniably works. The last method is to add sweetener as above, then cold crash the bottles once they carb up and keep them in the refrigerator until drinking. This naturally takes a good chunk of fridge space, and it's recommended to drink....relatively quickly, in case you have any exceptionally cold-hardy yeast in there.
Some people also use non-fermentable sweeteners and priming sugar after fermenting out to get a sweet-tasting cider. Examples include non-fermentable sugars (lactose) or artificial sweeteners (nutra-sweet, stevia, etc.). Personally, I don't much like this method, but ymmv.
I typically just choose one - sweet and still or carbonated and dry. Depends how important it is to you. If I ever start kegging, I may consider carbonating sweet ciders, but I like dry sparkling applewine perfectly fine.