Dynamic named range won't go to last entry

[LoriD]

I have a named range, which I set up exactly as stated on the Ozgrid site. I need columnD range to expand down as many rows as there are text or numeric entries. My formula in the Refers to box is:
=OFFSET(MaintRecords!$D$1,0,0,COUNTA(MaintRecords!$D:$D),1)
I have data in rows 1 through 82 yet when I select my range, it only selects up to row 65. Why might be it be stopping there?

 

[Andrew Poulsom]

Any empty cells? What does COUNTA return when applied to range D1:D82?

 

[Aladin Akyurek]

On 2002-10-03 12:02, LoriD wrote:
I have a named range, which I set up exactly as stated on the Ozgrid site. I need columnD range to expand down as many rows as there are text or numeric entries. My formula in the Refers to box is:
=OFFSET(MaintRecords!$D$1,0,0,COUNTA(MaintRecords!$D:$D),1)
I have data in rows 1 through 82 yet when I select my range, it only selects up to row 65. Why might be it be stopping there?

What kind of data (numeric or text) do you have in D?
This message was edited by Aladin Akyurek on 2002-10-03 12:36

 

[LoriD]

1. Yes, there are blanks, however they are well within the range that's getting selected. It's stopping in the middle of numbers.

2. Excel returns 63, when I do COUNTA(D1:D82)
That is the correct number of cells that contain any data, except for the blanks.

3. I have a column heading in D1, then the rest of the column contains numbers, blanks, or "N/A".

 

[Aladin Akyurek]

On 2002-10-03 12:48, LoriD wrote:
1. Yes, there are blanks, however they are well within the range that's getting selected. It's stopping in the middle of numbers.

2. Excel returns 63, when I do COUNTA(D1:D82)
That is the correct number of cells that contain any data, except for the blanks.

3. I have a column heading in D1, then the rest of the column contains numbers, blanks, or "N/A".

May I suggest you to use the method I described many times at this site & elsewhere? See
http://www.mrexcel.com/board/viewtopic.php?topic=16207&forum=2&7

Forget about using COUNT/COUNTA in defining a range by means of a dynamic formula (that is, with OFFSET).

You should eliminate #N/A's from D. Is that a VLOOKUP formula that returns those #N/A's?

Aladin
This message was edited by Aladin Akyurek on 2002-10-03 12:56

 

[Andrew Poulsom]

You have answered your own question.

The dynamic range reference uses COUNTA so it only counts 63 cells and uses that as an OFFSET. You can't use this dynamic range method if there are blanks.

No doubt someone will post an alternative.

 

[LoriD]

Thanks guys - and Aladdin, I did find that post already, it's just kind of over my head. Thanks again.

 

[Aladin Akyurek]

On 2002-10-03 12:57, LoriD wrote:
Thanks guys - and Aladdin, I did find that post already, it's just kind of over my head. Thanks again.

Please no, it's not that difficult at all.

Care to answer the following questions:

1) What formula returns #N/A in column D?

2) Is there any other column or columns that change at the same time as D?

 

[LoriD]

Aladdin,
Users enter either a value (mileage) in this column, leave it blank if it is not available, or enter N/A if mileage does not apply to this record.
This is actually a table of maintenance records, A:N, which a user is simply keying data into. Those are the changes that are typically made to this spreadsheet.
I have another sheet in this workbook that I want to update with data from the sheet we speak of, which is why I'm trying to name the range.

 

[Aladin Akyurek]

On 2002-10-03 13:10, LoriD wrote:
Aladdin,
Users enter either a value (mileage) in this column, leave it blank if it is not available, or enter N/A if mileage does not apply to this record.
This is actually a table of maintenance records, A:N, which a user is simply keying data into. Those are the changes that are typically made to this spreadsheet.
I have another sheet in this workbook that I want to update with data from the sheet we speak of, which is why I'm trying to name the range.

Is it N/A or #N/A what they enter?