Ken Rockwell has a really good overview of this:

http://www.kenrockwell.com/tech/mpmyth.htm

My general take: Most modern camera can take really good images that print at most common sizes. If you want to print really large (> 30" on the long size) you might need to be more careful about image selection and preparation, but I print at 13x19 regularly with images taken with some cropping without problem.

One thing that's forgotten is that in general, larger prints are going to be viewed further away so they don't need as much detail, so they can be printed with a lower pixel-per-inch value. It's always nice to take a gigapixel panorama and print it out so you can do the "where's waldo" on a 40x30 print with a magnifying glass, but in the real world, that rarely happens.

If you do have a print that simply doesn't have enough pixels to print well at a given size, you still have options. Photoshop's image resizing command works pretty well as growing an image, and there are various third party tools/plug-ins you can use. When I need to ramp up the pixel count or optimize an image to a specific size/usage pixel density I use the one from onOne and I think it works very well most of the time (some images simply can't be saved).

So the short answer is, try for at least 150 pixels per inch on larger prints and you'll do fine. There are tools to help if you are running short, but honestly, what I do is print the thing out and study the results, and I only go into corrective measures if the print isn't good. We can talk about tech details all we want, but what matters is how it looks on paper, so what you need to do is print it, study it, and then decide if you need to improve the print...