Is it possible to create custom workflows which can update list/libraries across a site collection (under same web app)?
Appreciate any help/guidance.
The "problem" with SharePoint 2007 workflows is impersonation, workflows run under initator account (user who started the workflow). The workaround this the easiest way is to create a web service that is performing the tasks the you need, you simply need to invoke this web service from your custom activity/code.
You web service can use regular SharePoint API to perform tasks as necessary. SPSite, SPWeb etc.. will do just fine.