You will get conflicting advice regarding just about any technology purchase or upgrade you ever consider. With every change some people love it and some people hate it. Lion has generated more passionate responses than most (although if you go back and look at some of the posts from when Snow Leopard first came out you will see a lot of negative comments there as well). This is most likely due to the combination of new features that are very controversial (versions, autosave, Launchpad, Mission Control), the loss of support for older Power PC software, and that some users continue to have stability issues.
For answers to your Adobe questions look here.
Bottom line, as Kappy says, if you want iCloud you need to upgrade. If you do want to upgrade, make sure the software you currently use is compatible with Lion or has a compatible upgrade available that you are willing to buy. Your hardware should be fine running Lion. You will be happiest if your MBP has at least 4 GB of memory. If you run memory-intensive programs or tend to have lots of apps open simultaneously put in as much memory as you can afford. A two year old MBP should support 8 GB. That's what I have in my late 2008 MacBook.