Formula error

[Melanie]

Formula: ='Small CI Lighting'!B9:F9 returns #VALUE! in one
column.field. But, if I insert a new column, the correct
value is returned. So, if I create the new column to the
right of the not working one, it works. When I delete the
non working column, the one that was working no longer
works anymore. What is going on here? Half of the
iterations of this formula work correctly. The formats are
the same...
Thanks

 

[Bill Kuunders]

Is there a hidden column on the Small CI Lighting sheet?
Regards
Bill K

 

[Anon]

Formula: ='Small CI Lighting'!B9:F9 returns #VALUE! in one
column.field. But, if I insert a new column, the correct
value is returned. So, if I create the new column to the
right of the not working one, it works. When I delete the
non working column, the one that was working no longer
works anymore. What is going on here? Half of the
iterations of this formula work correctly. The formats are
the same...
Thanks

='Small CI Lighting'!B9:F9
is not a valid formula (if entered in a single cell, which I assume is what
you are doing as you don't mention arrays). Therefore the 'result' you get
will be unpredictable.
A single formula must have a single result; your formula returns an array of
5.

You can have, for example,
=SUM('Small CI Lighting'!B9:F9)

Hope this helps.