Named Ranges

  • Thread starter Thread starter Nick1966
  • Start date Start date
N

Nick1966

I need to set up a spreadsheet to calculate ranges of products and
quantities in different regions and I would prefer to use names rather than
cell references. I can set up a formula using names if the range is
continuous, however I want to use ranges that are not continuous, the
formula returns #VALUE#.

Excel will allow you to define non continuous ranges when defining but I
cannot get the formula to work, please see example below

Price_per_Item defined as Sheet1!$c$4:$c$7,Sheet1!$c$10:$c$13
Quantity defined as Sheet1!$d$4:$d$7,Sheet1!$d$10:$d$13

Formula = Price_per_Item*Quantity

All cells are formatted correctly and I am using Excel 2000

Am I doing something wrong or isn't it possible to define ranges in this
way

Thanks

Nick Bradbury
 
The formula as written is not valid because you have not defined whic
of the multiple values to multiply. The usual way of handling thi
would be to put this formula into (say) cell E4 and copy down. Canno
see why this is not suitable in your case :-

=C4*D4

The INDEX() function can handle non-contiguous ranges, but you wil
still need to let it know which of your 2 areas to use. See Excel Help
 
Back
Top