Formula disappears inside a named range

I have a named range ... L2:L35 has been named 'ChosenClassList'.

I keep typing the following formula into L2 ...

=hlookup(ClassSelected,$C$1:$J$35,row())

where 'ClassSelected' is a named cell.

I then copy/drag that formula down to the bottom of the named rnage ( ie: down to L35), but the moment I do this, the formula within each of the cells is replaced by the text I was hoping each cell would display.

The formulae are gone. There is only text there in each of the cells. The formula bar shows there is no formula in the cells, only text.

I have erased it and done it over and over and I keep getting the same issue.

I changed the formula to an Index/Match formula and the same thing happened.

Is there something about creating formulae inside a named range that I'm not aware of ?

I'm using Microsoft Office Excel 2007

If you are able to open the attached file, you'll find the relevant sheet is called 'LookupTables'

Can you try it again, this time after entering the formula and filling down, can you leave that sheet, then go back into it and see if the formulae are still there ?

If no-one else is finding an issue with it, yet I certainly am, where do I go from here ?

Can I ask someone to open the attachment, write the formula in the named range and fill down. Can you then save it, close it down and then re-enter it.

It's when I do that, I find the formulae completely gone and text has over-written it