It depends on what you use it for.
If you use it for a lot of rendering, encoding or realtimeapps like sequencers that has softsynts and plugins that play in realtime then you need a better cpu first and more Ram later (you really need both in that scenario, but if you had to choose from one thing in that scenario then i would buy a better cpu first)
On the other hand: If you want to play games and your graphics card scales well with your cpu then more Ram will definitelly benefit you more(besides from buying a new graphic card).
As a rule windows xp needs much more Ram than win9x/winme because it is actually more efficient in letting the programs use as much memory as possible.
If you have 512 Mb that often results in a slowdown of normal windowstasks when you exit a program that has used a lot of RAM.
Some programs also don't work as smooth as they should because windows reads and writes to the pagingfile too much. There are some workarounds around this, but they mean more fiddling and less time spent on the apps themselves and they usually don't work that well.
So, to get a "smooth" experience from a windowssession where you use some "heavier programs" you really should opt for 1 Gig of Ram.
But as I said:It depends a lot on what you mostly use your computer for.
If you are the "Joe-normal" user who just surf the net, writes some worddocuments and plays some mp3;s, then even your system is overkill!
If you play a lot of games then I would actually look for more memory before a cpu (if you can't afford a better graphic card), but it depends on what kind of system you already have, and on the game itself.
Many homeusers nowadays also do a lot of encoding/decoding (to and from divx, mp3, wma and so on)
compression/decompression(zipping,raring) and things like that. These processes are almost 100% cpu-dependant. The same processortype, for example p4c does it propportionally faster to the increase in processorspeed.
If you are into making music with the pc using software synthesizers and effects plug-ins, then you need the fastest cpu possible. On the other hand: If your music is more based around samples and audiotracks then more Ram is needed. In most cases both are needed though if you do some kind of audio/video-editing or content creation.
If you on the other hand are a die-hard photoshop user and scans in huge files then more Ram is almost a must. In most cases the rendering processes with most modern cpu;s are already fast enough for most cases.
In general video-editing is the one thing that benefits most from using a lot of Ram, even if you are a homeuser these days(with cheap dv-cameras and great videoediting apps). That is if you mostly use it for cutting and don't use a lot of plugins or use realtime rendering/preview(which need more cpu). The editingprocess will get a noticeable increase in speed and it will also feel a lot smoother.
But again when you do the final rendering(when the actuall output-file is created) then it's all cpu again. But usually it is done during the night, in which case it is not as important( if it gets done before the morning that is)
For the average non gameplaying homeuser: 512 mb with winxp and a P3 and up is enough. Faster Cpu is mostly noticeable for this user when zipping/unzipping, coding/encoding, if they ever do that.
For games: 1 gig for more recent games, but you can live with lesser Ram. Cpu often less important than graphics card.
For producing music: 1 gig or more.(if you use the big programs like cubase sx, reason ,pro-tools, wavelab and lot of plugs and samplers all at the same time). If you use a lot of plugins and synts:Fastest cpu possible with SSE2 in it which means a pentium-4 or amd-64.
For videoediting: as much RAM as possible(restricted by OS). 1 Gig minimum if you are serious preferably more. If you also do realtime rendering and use a lot of plugs: Again fastest cpu possible.
P4 seems to do a greater job here, but I am not sure. The benchtests that i have seen here in anand may possbly favor the p4;s over amd-64;s in this area because of the choosen aps. Would like to see more of that(as i would love to see more comparasions between p4 and amd-64 for plug-ins/vst-instruments and not just for some dull waves in cubase-sx.
You really should analyse how you use your pc. In some cases a better cpu gives you a great performancelift but in other cases Ram is more important. Also: all the parts in the pc are more or less co-dependant. Have you done all you can to overclock your pc?
If you , for example have the fastest 3d-cards (like Geforce 6800 ultras) and want to cram everything out of doom 3, then you really need the fastest cpu/mb possible. If you , on the other hand has a p4 1.8 ghZ and , say a geforce ti-4200 then that particular game won't benefit from a better cpu, because the graphic card is the bottleneck.
Choose wisely!