Suppose I have a index.html hosted somewhere on the internet and I want it to use a CSS stylesheet which is stored as a Static Resource on a Salesforce Org.
Is this possible at all?
Yes, if you configure a Site. Go to Setup > Develop > Sites, create a new Site, and note the URL. Afterwards, you need to go to Setup > Develop > Static Resources, and upload your resource. Make sure that the "Cache Control" is set to "Public". Finally, you can use the resource with the appropriate URL, which will be something like https://configured-domain.naXX.force.com/resource/resourceName
.