Formula result (using F9) is different than what is displayed in the cell

Hi All,

I've had a very frustrating few hours trying to make a table populate dynamically based on the value of a particular cell. The formula is quite complicated as it references 2 other sheets and I have spent hours going through it section by section in different cells using the F9 function in the formula bar and I'm certain that it is correct because when I highlight the entire formula in the formula bar and press F9 I get the correct result every time, in every cell.

In the first row of cells containing the formula I get results (all correct, or all incorrect, depending on the value of the reference), then all the following rows give a #NUM! error.

I would just keep working at debugging it but I'm stumped because the formula is working correctly, it is just displaying incorrectly in the cells.

This is on Excel 2007. Has anyone experienced anything like this before?

My explanation is a bit rambling as it's hard to describe in text when you can't see the spreadsheet so if I can clarify anything please let me know.

Thanks for your time!