soteman2005
New Member
- Joined
- Nov 24, 2005
- Messages
- 32
Hi,
I am creating a model in excel using range names for every row which works really well. The model is run across several years which are allocated a column each. I need to sum some cells which are all in 1 year e.g. =sum(b1:b5) but when i use the range names for row 1:row 5, excel sums the entire named range from A1:IX1 to A5:IX5, instead of just the cells in the active column.
Does anyone know a way to do this? I'm thinking that there might be an intersect function or something which will allow me to sum the intersecting cells of the named column range and named row ranges?
Similarly, I would also like to be able to reference a cell from the previous year to do an average, so is there a way to do that using the range name i.e I am calculating a 2006 average but I need the 2005 value for susbcribers, but the name range gives me the 2006 value.
Many thanks in advance
Adam
I am creating a model in excel using range names for every row which works really well. The model is run across several years which are allocated a column each. I need to sum some cells which are all in 1 year e.g. =sum(b1:b5) but when i use the range names for row 1:row 5, excel sums the entire named range from A1:IX1 to A5:IX5, instead of just the cells in the active column.
Does anyone know a way to do this? I'm thinking that there might be an intersect function or something which will allow me to sum the intersecting cells of the named column range and named row ranges?
Similarly, I would also like to be able to reference a cell from the previous year to do an average, so is there a way to do that using the range name i.e I am calculating a 2006 average but I need the 2005 value for susbcribers, but the name range gives me the 2006 value.
Many thanks in advance
Adam