Hope someone can help with this issue.

I have a macro that is adding a person to an indexed range. Everything is
successful in adding and redexing the Data range except....in the macro and
for this test excercise I have a copy and paste from another cell in the
worksheet of Dates the value of "1"into the Data range named Data. I have
even left a blank column inbetween the Data range and the row index for the
index formula [Tim Smith Example]. I was thinking that the blank column would
set up my dynamic range well but instead I am getting the results [2nd Tim
Smith Example]. Can anyone see what I am doing wrong? I assume it is
something in the formula that I do not understand. Thanks in advance.

46 Row
Tim Smith Index

0 1

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E:$E),COUNTA( Dates!$3:$3))

46 Row
Tim Smith Index

1 1 1 1 1 1

--
Message posted via http://www.officekb.com

Look in the help index for COUNTA to see your problem

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:801f1b5a8f9af@uwe...
Hope someone can help with this issue.

I have a macro that is adding a person to an indexed range. Everything is
successful in adding and redexing the Data range except....in the macro
and
for this test excercise I have a copy and paste from another cell in the
worksheet of Dates the value of "1"into the Data range named Data. I have
even left a blank column inbetween the Data range and the row index for
the
index formula [Tim Smith Example]. I was thinking that the blank column
would
set up my dynamic range well but instead I am getting the results [2nd Tim
Smith Example]. Can anyone see what I am doing wrong? I assume it is
something in the formula that I do not understand. Thanks in advance.

46 Row
Tim Smith Index

0 1

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E:$E),COUNTA( Dates!$3:$3))

46 Row
Tim Smith Index

1 1 1 1 1 1

--
Message posted via http://www.officekb.com

That wasn't helpful as I have already been to the help index.

Don Guillett wrote:
Look in the help index for COUNTA to see your problem

Hope someone can help with this issue.

[quoted text clipped - 22 lines]

1 1 1 1 1 1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

46 Row
Tim Smith Index

1 1 1 1 1 1

I have no idea what that's supposed to represent....

Try this:

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536) ,COUNTA(Dates!$E$3:$IV$3))


--
Biff
Microsoft Excel MVP


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:801fe075342aa@uwe...
That wasn't helpful as I have already been to the help index.

Don Guillett wrote:
Look in the help index for COUNTA to see your problem

Hope someone can help with this issue.

[quoted text clipped - 22 lines]

1 1 1 1 1 1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

Then it's not dynamic, right?

T. Valko wrote:
46 Row
Tim Smith Index

1 1 1 1 1 1

I have no idea what that's supposed to represent....

Try this:

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536 ),COUNTA(Dates!$E$3:$IV$3))

That wasn't helpful as I have already been to the help index.

[quoted text clipped - 5 lines]

1 1 1 1 1 1

--
Message posted via http://www.officekb.com

Then it's not dynamic, right?

T. Valko wrote:
46 Row
Tim Smith Index

1 1 1 1 1 1

I have no idea what that's supposed to represent....

Try this:

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$65536 ),COUNTA(Dates!$E$3:$IV$3))

That wasn't helpful as I have already been to the help index.

[quoted text clipped - 5 lines]

1 1 1 1 1 1

--
Message posted via http://www.officekb.com

Then it's not dynamic, right?

No, it's dynamic based on:

Height = COUNTA(Dates!$E$3:$E$65536)
Width = COUNTA(Dates!$E$3:$IV$3)



--
Biff
Microsoft Excel MVP


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:802011fc3c66f@uwe...
Then it's not dynamic, right?

T. Valko wrote:
46 Row
Tim Smith Index

1 1 1 1 1 1

I have no idea what that's supposed to represent....

Try this:

=OFFSET(Dates!$E$3,0,0,COUNTA(Dates!$E$3:$E$6553 6),COUNTA(Dates!$E$3:$IV$3))

That wasn't helpful as I have already been to the help index.

[quoted text clipped - 5 lines]

1 1 1 1 1 1

--
Message posted via http://www.officekb.com

COUNTA

See Also

Counts the number of cells that are not empty and
--
Don Guillett
Microsoft MVP Excel
SalesAid Software


"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:801fe075342aa@uwe...
That wasn't helpful as I have already been to the help index.

Don Guillett wrote:
Look in the help index for COUNTA to see your problem

Hope someone can help with this issue.

[quoted text clipped - 22 lines]

1 1 1 1 1 1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

Yes, I see, thanks - Is there any other solution to a dynamic range? Since
the range is picking up 3 extra columns can I put another offset -3 somewhere?


Don Guillett wrote:
COUNTA

See Also

Counts the number of cells that are not empty and
That wasn't helpful as I have already been to the help index.

[quoted text clipped - 5 lines]

1 1 1 1 1 1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1

instead of counta( etc use
match(9999999,$3:$3)-5
and check by touching the f5 key and typing in the name of the defined range
if text use "zzzzzzzzzz"
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Carrie_Loos via OfficeKB.com" <u34134@uwe wrote in message
news:802027b0c3f82@uwe...
Yes, I see, thanks - Is there any other solution to a dynamic range? Since
the range is picking up 3 extra columns can I put another offset -3
somewhere?


Don Guillett wrote:
COUNTA

See Also

Counts the number of cells that are not empty and
That wasn't helpful as I have already been to the help index.

[quoted text clipped - 5 lines]

1 1 1 1 1 1

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200802/1