I have some idea of what Namecoin was trying to achieve, but as of yet I've never seen it actually used. Is it being used for anything of significance yet, or are there plans by anyone to do so in the short to medium term?
If a distributed, first-come-first-serve domain name system for the .bit
top-level domain (TLD) is significant, then yes, it has done something significant. There are several draft proposals for additional namespaces to cover things like messaging, identity, and .tor
and .i2p
TLDs.
If I were to register a .bit domain name and host a website, can everybody with a standard internet connection setup access it, or is special software or other setup required?
There is and will likely always be some kind of special setup required to access domains that have not been approved by ICANN. If you're unable to resolve dot-bit.bit, then your domain name servers are not configured for it. I've used the DD-WRT instructions in the How To Browse .bit Domains guide with success. The servers change infrequently.