Formulae not calculating...

I've been using Excel heavily for over a decade, but this problem's got me totally and utterly stumped. I've got a worksheet which is a list of documents. In a series of columns I've got metadata about those documents (date, author etc). One of the fields is a hyperlink to the doc itself, constructed like this:

=HYPERLINK($B$1&"path","display name")

Cell B1 is the first part of the filepath, so this makes it easy to move the spreadsheet and the docs to a different file area (e.g. a CD) with only one cell to change (almost like a relative file path, such as you would use in html).

The spreadsheet has been working just fine for about two months, and I've been populating the spreadsheet with great success. However, yesterday I went to edit one of the Hyperlink formulae and when I pressed return it just showed the formula, not the hyperlink. If I copy another working hyperlink formula in from another cell, it works, but if I click to edit the formula and then press return (even without making a change), it just shows the formula again, not the hyperlink. All of my hyperlinks are now doing this, in every cell.

Things I've checked:

Format of the cells - I've tried various formatting options and nothing works.
Auto-Calculate - I've tried this on and off, plus F9 and shift-F9
Conditional Formatting - none applied, and I've tried clearing all.

Any ideas would be greatly appreciated.

thanks for reading.

PS - this may be a keyboard shortcut; I have two cats that often walk around on the keyboard and they have a habit of finding the most obscure keyboard shortcuts imaginable!

Thanks for your reply.

Yes, If I type =2+3 into the cell, it shows the formula, not the result. The problem affects some columns of the worksheet, but not all. If I type =2+3 into some cells, then it calculates it.

Incidentally, I've just found a workaround! Previously, I'd tried using 'Format Painter' to copy in the format of another cell where the formula works, but that failed (thus my comment '1' above). However, if I skip to an untouched area of the spreadsheet a long way from my populated cells, and copy the format of that cell to the broken cell, then it works. So my assumption (1) above was wrong.

I'd still like to know what's wrong though!...

I must have been mistaken, the Format Painter trick didn't work, it was the other thing I tried, which was to copy a random blank cell to the dodgy cell, and then paste in the text of the link that I want.

Formatting checked and it's identical between working and not working cells.

If you see formula instead try:

Go to Ribbon / Formulas and in Formulas Auditing unclick Show Formulas button.

Source: http://best-excel-tutorial.com/basic/84-excel-faq

Thanks - I've tried that already. That shows and hides all formula, whereas this is just a few cells that I happen to have edited since yesterday.

Any ideas from anyone? I'm currently ploughing through loads of cells copying in a blank cell and then pasting the formula text in to get it to work.

It's very strange how Format Painter from the blank cell to the formula cell doesn't work, but pasting as above does..