Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range-name Behaviour
I think I may have lost the plot.
If anyone can spare a couple of minutes to expain this to me I would be very grateful. Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4 (definitely absolute references). If I enter =John in C1 it returns a value error as I would expect. Obviously, you cannot fit the contents of 3 cells into only one cell. What I do not understand is that if I now copy/paste or autofill C1 down the column to say C5 I get the expected value errors in rows 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of John I get the value of cells A2,A3&A4 respectively. Why dont I get value errors in all the cells through C1 to C5? I bet I am missing something really obvious... Many thanks in advance, Peter |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range-name Behaviour
Hi Peter,
This behavior is called implicit intersection. If a formula refers to a named range and only a one-cell operand is possible for the function, than it uses the intersection of the formula and the named range (either row or column). Note that this does not work for functions that can accept multi-cell operands (like MAX, AVERAGE, etc). It also doesn't work (not anymore, it used to work in Excel 5) for arguments for UDFs; then you need explicit intersection, like =MyUDF(Rangename 2:2) or force a one-cell reference through implicit intersection with another function, like =MyUDF(Rangename + 0). -- Kind regards, Niek Otten Microsoft MVP - Excel "Peter" wrote in message ... |I think I may have lost the plot. | | If anyone can spare a couple of minutes to expain this to me I would be very | grateful. | | Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4 | (definitely absolute references). If I enter =John in C1 it returns a value | error as I would expect. Obviously, you cannot fit the contents of 3 cells | into only one cell. What I do not understand is that if I now copy/paste or | autofill C1 down the column to say C5 I get the expected value errors in rows | 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of | John I get the value of cells A2,A3&A4 respectively. Why don't I get value | errors in all the cells through C1 to C5? | | I bet I am missing something really obvious... | | Many thanks in advance, Peter | | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range-name Behaviour
Niek,
Thank you very much. I even use intersection but I did not recognise it here. I feel just a little embarrassed. Peter. "Niek Otten" wrote: Hi Peter, This behavior is called implicit intersection. If a formula refers to a named range and only a one-cell operand is possible for the function, than it uses the intersection of the formula and the named range (either row or column). Note that this does not work for functions that can accept multi-cell operands (like MAX, AVERAGE, etc). It also doesn't work (not anymore, it used to work in Excel 5) for arguments for UDFs; then you need explicit intersection, like =MyUDF(Rangename 2:2) or force a one-cell reference through implicit intersection with another function, like =MyUDF(Rangename + 0). -- Kind regards, Niek Otten Microsoft MVP - Excel "Peter" wrote in message ... |I think I may have lost the plot. | | If anyone can spare a couple of minutes to expain this to me I would be very | grateful. | | Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4 | (definitely absolute references). If I enter =John in C1 it returns a value | error as I would expect. Obviously, you cannot fit the contents of 3 cells | into only one cell. What I do not understand is that if I now copy/paste or | autofill C1 down the column to say C5 I get the expected value errors in rows | 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of | John I get the value of cells A2,A3&A4 respectively. Why don't I get value | errors in all the cells through C1 to C5? | | I bet I am missing something really obvious... | | Many thanks in advance, Peter | | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Range-name Behaviour
< I feel just a little embarrassed
Don't! This behavior is known to very few users. If you use intersection, consider yourself an Excel expert. -- Kind regards, Niek Otten Microsoft MVP - Excel "Peter" wrote in message ... | Niek, | | Thank you very much. I even use intersection but I did not recognise it | here. I feel just a little embarrassed. | | Peter. | | "Niek Otten" wrote: | | Hi Peter, | | This behavior is called implicit intersection. | If a formula refers to a named range and only a one-cell operand is possible for the function, than it uses the intersection of | the formula and the named range (either row or column). | Note that this does not work for functions that can accept multi-cell operands (like MAX, AVERAGE, etc). | It also doesn't work (not anymore, it used to work in Excel 5) for arguments for UDFs; then you need explicit intersection, like | =MyUDF(Rangename 2:2) or force a one-cell reference through implicit intersection with another function, like =MyUDF(Rangename + | 0). | | -- | Kind regards, | | Niek Otten | Microsoft MVP - Excel | | | "Peter" wrote in message ... | |I think I may have lost the plot. | | | | If anyone can spare a couple of minutes to expain this to me I would be very | | grateful. | | | | Suppose I define a 3 cell range-name, say John, to refer to $A$2:SA$4 | | (definitely absolute references). If I enter =John in C1 it returns a value | | error as I would expect. Obviously, you cannot fit the contents of 3 cells | | into only one cell. What I do not understand is that if I now copy/paste or | | autofill C1 down the column to say C5 I get the expected value errors in rows | | 1 and 5 but in rows 2,3&4 i.e. the same rows as used in the definition of | | John I get the value of cells A2,A3&A4 respectively. Why don't I get value | | errors in all the cells through C1 to C5? | | | | I bet I am missing something really obvious... | | | | Many thanks in advance, Peter | | | | | | | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
for...next funny behaviour | Excel Discussion (Misc queries) | |||
Strange if(***) behaviour? | Excel Discussion (Misc queries) | |||
Excel2000: Weird behaviour in VBA | Excel Discussion (Misc queries) | |||
Odd toolbar icon behaviour | Excel Discussion (Misc queries) | |||
Strange behaviour | Excel Worksheet Functions |