If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
refer to range in another sheet
I have the following block of numbers in a worksheet (let's say Sheet1), the
column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
#2
|
|||
|
|||
refer to range in another sheet
Hi,
could you post your formula with an explanation of what you want to achieve, thanks "Smallweed" wrote: I have the following block of numbers in a worksheet (let's say Sheet1), the column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
#3
|
|||
|
|||
refer to range in another sheet
Hi,
You don't give a clear indication of what your formula is. I don't understand what you mean by T1 to T5. If these were named ranges you would have got an error because those are illegal names for a named range. Here's a simple array formula referring to named ranges on another sheet. perhaps you can get the syntax from that. As you can see it isn't necessary to have the sheet name when using a named range =SUM(IF(YY1="Y",YY2)) The 2 ranges being YY1 & YY2 Mike "Smallweed" wrote: I have the following block of numbers in a worksheet (let's say Sheet1), the column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
#4
|
|||
|
|||
refer to range in another sheet
Sorry, as you say I shouldn't have used T1-T5 in my example. Ok, I've got 5
columns of data, named Terry1, Terry2, Terry3, Terry4 and Terry5 respectively. I can use the reference Terry1:Terry5 to refer to the whole block in formulae in the same sheet. I would like to use the same range in another sheet but Sheet1!Terry1:Terry5 gives me #VALUE. Any ideas of an alternative syntax that would work? "Mike H" wrote: Hi, You don't give a clear indication of what your formula is. I don't understand what you mean by T1 to T5. If these were named ranges you would have got an error because those are illegal names for a named range. Here's a simple array formula referring to named ranges on another sheet. perhaps you can get the syntax from that. As you can see it isn't necessary to have the sheet name when using a named range =SUM(IF(YY1="Y",YY2)) The 2 ranges being YY1 & YY2 Mike "Smallweed" wrote: I have the following block of numbers in a worksheet (let's say Sheet1), the column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
#5
|
|||
|
|||
refer to range in another sheet
I gave you the answer in my previous post
=MAX(Terry1:Terry5) Works from any sheet, you don't need to use the sheet name with named ranges Mike "Smallweed" wrote: Sorry, as you say I shouldn't have used T1-T5 in my example. Ok, I've got 5 columns of data, named Terry1, Terry2, Terry3, Terry4 and Terry5 respectively. I can use the reference Terry1:Terry5 to refer to the whole block in formulae in the same sheet. I would like to use the same range in another sheet but Sheet1!Terry1:Terry5 gives me #VALUE. Any ideas of an alternative syntax that would work? "Mike H" wrote: Hi, You don't give a clear indication of what your formula is. I don't understand what you mean by T1 to T5. If these were named ranges you would have got an error because those are illegal names for a named range. Here's a simple array formula referring to named ranges on another sheet. perhaps you can get the syntax from that. As you can see it isn't necessary to have the sheet name when using a named range =SUM(IF(YY1="Y",YY2)) The 2 ranges being YY1 & YY2 Mike "Smallweed" wrote: I have the following block of numbers in a worksheet (let's say Sheet1), the column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
#6
|
|||
|
|||
refer to range in another sheet
Hi,
use the just your names, i.e if you want to sum the range =sum(Terry1:Terry5) "Smallweed" wrote: Sorry, as you say I shouldn't have used T1-T5 in my example. Ok, I've got 5 columns of data, named Terry1, Terry2, Terry3, Terry4 and Terry5 respectively. I can use the reference Terry1:Terry5 to refer to the whole block in formulae in the same sheet. I would like to use the same range in another sheet but Sheet1!Terry1:Terry5 gives me #VALUE. Any ideas of an alternative syntax that would work? "Mike H" wrote: Hi, You don't give a clear indication of what your formula is. I don't understand what you mean by T1 to T5. If these were named ranges you would have got an error because those are illegal names for a named range. Here's a simple array formula referring to named ranges on another sheet. perhaps you can get the syntax from that. As you can see it isn't necessary to have the sheet name when using a named range =SUM(IF(YY1="Y",YY2)) The 2 ranges being YY1 & YY2 Mike "Smallweed" wrote: I have the following block of numbers in a worksheet (let's say Sheet1), the column ranges named T1, T2, T3, T4 and T5: T1 T2 T3 T4 T5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 4 5 1 2 3 5 7 1 2 5 7 10 1 2 5 10 12 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 1 2 5 10 15 In the same sheet, I can put together an array formula that refers to the whole block by using the syntax T1:T5. However, if I try to use the same array formula in a different sheet using Sheet1!T1:T5 I get #VALUE. Can anyone tell me what's wrong with my syntax and if there's any way of referring to this range in another sheet in terms of the existing range names (I don't want to use cell references, i.e. A1:E14)? |
Thread Tools | |
Display Modes | |
|
|