Dynamic named range won't go to last entry

LoriD

Board Regular
Joined
Apr 1, 2002
Messages
141
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?
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Board Regular
Joined
Apr 1, 2002
Messages
141

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Board Regular
Joined
Apr 1, 2002
Messages
141
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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?
 

Forum statistics

Threads
1,144,219
Messages
5,723,076
Members
422,477
Latest member
pete101

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top