My Dynamic Named Range isn't working

[Carrie_Loos via OfficeKB.com]

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

 

[Don Guillett]

Look in the help index for COUNTA to see your problem

 

[Carrie_Loos via OfficeKB.com]

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

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

 

[T. Valko]

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

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

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

 

[Don Guillett]

COUNTA

See Also

Counts the number of cells that are not empty and
--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

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

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

 

[Carrie_Loos via OfficeKB.com]

Then it's not dynamic, right?

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]

 

[Carrie_Loos via OfficeKB.com]

Then it's not dynamic, right?

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]

 

[Carrie_Loos via OfficeKB.com]

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?

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]

 

[Don Guillett]

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
(e-mail address removed)

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?

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

 

[T. Valko]

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

Then it's not dynamic, right?

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

 

[Carrie_Loos via OfficeKB.com]

Thanks Don - You gave me an idea that ended up working well. I tried the
match but it made the range too big and I need to name the range in little
pieces so I can place a Vlookup formula in the range and cut down on the run
time. Bottom line, I see that my 4 columns to the left and top two rows are
causing my issue. Therefore, I placed negative numbers at the end of the
CountA formula and it worked famously. It will be interesting to see how it
behaves once I write it into the vb. Hopefully it's not disasterous

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

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"

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

[quoted text clipped - 10 lines]

 

[Don Guillett]

Glad to help

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

Thanks Don - You gave me an idea that ended up working well. I tried the
match but it made the range too big and I need to name the range in little
pieces so I can place a Vlookup formula in the range and cut down on the
run
time. Bottom line, I see that my 4 columns to the left and top two rows
are
causing my issue. Therefore, I placed negative numbers at the end of the
CountA formula and it worked famously. It will be interesting to see how
it
behaves once I write it into the vb. Hopefully it's not disasterous

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

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"

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

[quoted text clipped - 10 lines]

1 1 1 1 1 1