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)?

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)?

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)?

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)?

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)?

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)?