Well, can't say I'm an expert in videocards, but I myself purchased an ASUS ATI Radeon 9600 XT for Rome:Total War which has performed very well. Keeping in mind that I only have a 1 Ghz PIII processor and 512 MB of SDRAM the game has run very smoothly thse last few months, so I can heartily recommend this card which has a very good price/quality ratio. Of course, if you're going to play games that require a heavy duty videocard (like Doom III, Halflife II etc.) you're probably better off buying a higher end card, like a 9800 XT. Keep in mind though that those cards will probably cost twice as much as a 9600 XT.
I play mainly strategy games, so having high specs is not that important. However the likes of Age of Empires 3 needs something powerful. I need something that will last just over a year, then I will get a new computer when I go to uni.