problem naming range

[BorisS]

I have a range I'm defining in the name manager, and when I enter the formula

OFFSET(a1,0,0,count(a:a),10)

then shft-tab/tab to get the blinking highlight of what it's defining, it's
clearly seeing the right range.

however, three problems:

1) when I go to the named range dropdown, the newly created range isn't there
2) when I use it in a vlookup to test, it only recognizes the range as a
1-column range
3) as soon as I save the range as the formula above, it automatically
converts to

OFFSET([sheet]A1,0,0,count([sheet]a:a,10)

Any of these problems explainable (I can understand (3), but not the
others)? Am using 2003.

 

[Don Guillett]

OFFSET(a1,0,0,count(a:a),10)
OFFSET($a$1,0,0,count($a:$a),10)
and you probably want countA instead of count
excel will automatically change to the sheet you are on when creating the
formula
OFFSET(sheet1!$a$1,0,0,count(sheet1!$a:$a),10)

 

[BorisS]

and the other two issues? any thoughts?
--
Boris

OFFSET(a1,0,0,count(a:a),10)

OFFSET($a$1,0,0,count($a:$a),10)
and you probably want countA instead of count
excel will automatically change to the sheet you are on when creating the
formula
OFFSET(sheet1!$a$1,0,0,count(sheet1!$a:$a),10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have a range I'm defining in the name manager, and when I enter the
formula

OFFSET(a1,0,0,count(a:a),10)

then shft-tab/tab to get the blinking highlight of what it's defining,
it's
clearly seeing the right range.

however, three problems:

1) when I go to the named range dropdown, the newly created range isn't
there
2) when I use it in a vlookup to test, it only recognizes the range as a
1-column range
3) as soon as I save the range as the formula above, it automatically
converts to

OFFSET([sheet]A1,0,0,count([sheet]a:a,10)

Any of these problems explainable (I can understand (3), but not the
others)? Am using 2003.

 

[Don Guillett]

test

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

and the other two issues? any thoughts?
--
Boris

OFFSET(a1,0,0,count(a:a),10)

OFFSET($a$1,0,0,count($a:$a),10)
and you probably want countA instead of count
excel will automatically change to the sheet you are on when creating the
formula
OFFSET(sheet1!$a$1,0,0,count(sheet1!$a:$a),10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have a range I'm defining in the name manager, and when I enter the
formula

OFFSET(a1,0,0,count(a:a),10)

then shft-tab/tab to get the blinking highlight of what it's defining,
it's
clearly seeing the right range.

however, three problems:

1) when I go to the named range dropdown, the newly created range isn't
there
2) when I use it in a vlookup to test, it only recognizes the range as
a
1-column range
3) as soon as I save the range as the formula above, it automatically
converts to

OFFSET([sheet]A1,0,0,count([sheet]a:a,10)

Any of these problems explainable (I can understand (3), but not the
others)? Am using 2003.

 

[Roger Govier]

Hi Boris

As Don has said, using COUNTA rather than COUNT maybe the reason for not
having the correct number of Rows.
Since the width of the range is fixed at 10 in the formula, Then I cannot
conceive of any possibility where you would end up with a range 1 column
wide, unless the 10 was erroneously typed as 1.
What name did you give to your named range?

When I set up named ranges, having defined the name e.g. myData and the
range e.g. OFFSET($A$1,0,0,COUNTA($A:$A),10)
I always press the Add button, then OK to quit.

--
Regards
Roger Govier

and the other two issues? any thoughts?
--
Boris

OFFSET(a1,0,0,count(a:a),10)

OFFSET($a$1,0,0,count($a:$a),10)
and you probably want countA instead of count
excel will automatically change to the sheet you are on when creating the
formula
OFFSET(sheet1!$a$1,0,0,count(sheet1!$a:$a),10)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

I have a range I'm defining in the name manager, and when I enter the
formula

OFFSET(a1,0,0,count(a:a),10)

then shft-tab/tab to get the blinking highlight of what it's defining,
it's
clearly seeing the right range.

however, three problems:

1) when I go to the named range dropdown, the newly created range isn't
there
2) when I use it in a vlookup to test, it only recognizes the range as
a
1-column range
3) as soon as I save the range as the formula above, it automatically
converts to

OFFSET([sheet]A1,0,0,count([sheet]a:a,10)

Any of these problems explainable (I can understand (3), but not the
others)? Am using 2003.