Forum Discussion
Using names in formulas?
Hello, I have the following table: Items Jan Feb Mar Apples 7 2 3 Bananas 12 25 14 Oranges 10 8 6 What is the best way to use name ranges, so when I want to ex...
Assuming you have successfully defined the rows Apples, Bananas, Oranges and the column ranges Jan, Feb, Mar, then the value for a given month is obtained by intersecting a row and column range using a space as the operator. Examples are
25
= (Bananas Feb)
27
= SUM(Apples:Bananas Feb)
10
= SUM(Apples Feb, Oranges Feb)Life gets a little more complicated if you want to select months because the Names can get muddled with the A1-style column references
#NULL!
= Apples FEB:JAN
{7, 2}
= Apples TAKE(Items, ,2)
Thanks, Peter
this seems to work as long as the left column has words in each row. If I replace the words apples bananas and oranages with 1,2,3 it doesn't work. Any idea if it is possible to work with numbers?
Thanks again
this seems to work as long as the left column has words in each row. If I replace the words apples bananas and oranages with 1,2,3 it doesn't work. Any idea if it is possible to work with numbers?
Thanks again
It is just that naming from selection doesn't work because a name must start with an underscore or a letter. In addition it must not be a valid cell reference. Name manager and the Name box offer alternative ways of defining names. For example
= (January _1st)is a valid intersection assuming the names are defined appropriately.
- Thank you!