error using this simple formula

C

Calle

Hi!
I get error when I try to define a name for a dynamic number of rows why? Is
something wrong with this formula?

=OFFSET(importtotal!$A$1,0,1,COUNTA(importtotal!$A:$A),10)
 
D

Don Guillett

You have too many parameters. Look in the help index for OFFSET to see your
error.
 
C

Carim

Hi,

With :
=OFFSET(importtotal!$A$1,0,1,COUNTA(importtotal!$A:$A),10)

Make sure :
Your columns to offset is 1 ( and not 0 ), and that it is consistent
with the count number of columns which is equal to 10 ... otherwise
your range cannot be built ...

HTH
Cheers
Carim
 
J

JLatham

I don't think Excel is liking the way you're using OFFSET(). Try typing that
formula into a cell and you'll get a #Value error. I think, especially in
the case of an OFFSET() that is returning more than a single cell, Excel is
expecting it to be part of another function as:
=SUM(OFFSET(importtotal!$A$1,0,1,COUNTA(importtotal!$A:$A),10))

That's about the best I can do at trying to explain it, maybe someone else
can give a better explanation.
 
J

JLatham

Dave Peterson says it should work (worked for him) - I'd trust him on that!
<g> I didn't try it as a named formula.
 
C

Carim

Hi,

Agree with Dave ...
Test OK with me ...
May be the worksheet name ...

Cheers
Carim
 
C

Calle

I had to change the formula to this:
=OFFSET(importtotal!$A$1;0,1;COUNTA(importtotal!A:A);10)
I guess my version is bugged...
 
C

Calle

I see that didn't work either. It let me creat a name, but it won't work in a
pivot table
 
F

Farhad

use this formula:
=SUM(OFFSET(importtotal!$A$1,0,1,COUNTA(OFFSET(importtotal!$A$1,0,1,9999,1)),10))
but remember it would be dynamic just for 9999 lines and let me know if it
doesn't work

Farhad Hodjat
 
C

Calle

Hi!
I just found out you have to use ";" instead of "," in the sweish version.
found this formula on the swedish support site:

=FÖRSKJUTNING($B$2;0;0;ANTAL($B$2:$B$200);1)

thx anyway all people... Nice to see everyone come to help :)
 
Top