Formula error

  • Thread starter Thread starter Melanie
  • Start date Start date
M

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
 
Melanie said:
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
Click to expand...

='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.
 
Back
Top