Dynamic Range Not Working

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Takeadoe - 25 Sep 2006 17:16 GMT

I created a named range called HarvestData.

In the refers to box I typed the following:
=OFFSET(Harvest!$A$2,0,0,COUNTA(Harvest!$A:$A),COUNTA(Harvest!$1:$1))

I hit ok.

If I go back into the "Define Name" dialog box and select the name
"Harvest" and have it point to the actual range in the worksheet, it
highlights cells A2:B20. The problem is, there are data in columns A
to N. Why is it not recognizing the rest of the columns? I discovered
this problem because I'm referencing column h in a VLOOKUP and it is
not returning data that I know is there.

What am I doing wrong here?

Your help is really appreciated.

Mike

Reply to this Message

Bob Phillips - 25 Sep 2006 16:13 GMT

Perhaps it should be

=OFFSET(Harvest!$A$2,0,0,COUNTA(Harvest!$A:$A),COUNTA(Harvest!$2:$2))

Signature

HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

> I created a named range called HarvestData.
>
[quoted text clipped - 15 lines]
>
> Mike

Reply to this Message

JE McGimpsey - 25 Sep 2006 18:00 GMT

Your formula works fine for me, as long as I have data in row 1. Are you
sure you have data in each cell from A1:N1?

> I created a named range called HarvestData.
>
[quoted text clipped - 15 lines]
>
> Mike

Reply to this Message

Takeadoe - 25 Sep 2006 18:10 GMT

First, thank you for replying. Second, I do not have data in every
cell from A1:N1. I do have data in A2:N2 though, which is actually my
header row. Is that the problem?

Mike

> Your formula works fine for me, as long as I have data in row 1. Are you
> sure you have data in each cell from A1:N1?
[quoted text clipped - 18 lines]
> >
> > Mike

Reply to this Message