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?

 

[LoriD]

Aladin,

They just enter N/A.

Lori

 

[Aladin Akyurek]

On 2002-10-03 14:18, LoriD wrote:
Aladin,

They just enter N/A.

Lori

Perfect.

You have numeric as well as text values in D, including blanks. That means D is of mixed data type. We need to augment the method described in the quoted thread.

Here we go until we get it working as desired.

( 0.) Insert a worksheet to your workbook and name it, e.g., Admin.
( 1.) Activate Insert|Name|Define.
( 2.) Enter BigNum as name in the Names in Workbook box.
( 3.) Enter the following in the Refers to box.

9.99999999999999E+307

( 4.) Click OK.
( 5.) Activate Admin.
( 6.) In A2 enter: # of rows [ which is just a label ]
( 7.) In B2 enter:

=MAX(MATCH(BigNum,MaintRecords!D:D),MATCH(REPT("z",60),MaintRecords!D:D))

( 8.) In A3 enter: # of data recs [ which is just a label ]
( 9.) In B3 enter:

=B2-(CELL("Row",MaintRecords!D1)-1)

( 10.) In A4 enter: # of data columns [ which is just a label ]
( 11.) In B4 enter: 1 [ hardcoded number of columns in use ]
( 12.) Activate (again) Insert|Name|Define.
( 13.) Select the name you already defined for the range in column D.
( 14.) In the Refers to box, enter the following formula:

=OFFSET(MaintRecords!$D$1,0,0,Admin!$B$3,Admin!$B$4)

( 15.) Click OK.

If so desired, protect and hide the Admin sheet.

The above defines a named range regarding just the data in D. In fact, we could define a single name for the whole data area in A:N if the area expands/crimps in concert. If you would want that, just let me know.

Aladin


EDITed for an unimportant typo.
This message was edited by Aladin Akyurek on 2002-10-03 14:46

 

[LoriD]

That is perfect. Thank you so much, you are very patient! And yes, if you could help me define a single name for the whole range, that would be great.

 

[Aladin Akyurek]

On 2002-10-03 14:58, LoriD wrote:
That is perfect. Thank you so much, you are very patient! And yes, if you could help me define a single name for the whole range, that would be great.

Which column in A:N is the most reliable indicator of changes to the data area? One with dates or just numbers is the most ideal.

 

[LoriD]

Column B; They enter the date of service for each record.

 

[Aladin Akyurek]

On 2002-10-03 15:10, LoriD wrote:
Column B; They enter the date of service for each record.

Make a copy of your current workbook.

Change the formula in Step 7 to:

=MATCH(BigNum,MaintRecords!B:B)

Change the value in B4 in Admin to 14 (We have now 14 columns).

Change the formula in Step 9 to:

=B2-(CELL("Row",MaintRecords!B1)-1)

Activate Insert|Name|Define.
Enter DTable as name in the Names in Workbook box.
Enter the following formula in the Refers to box:

=OFFSET(MaintRecords!$A$1,0,0,Admin!$B$3,Admin!$B$4)

Click Add.

Select the old name regarding the range in D.
Click Delete.
Click OK.

What follows is very important info concerning how to access individual column ranges using the name DTable.

Suppose you want to know how many cells in D contain data (numbers plus N/A's). The following formula allows you to do that:

=COUNTA(INDEX(DTable,0,4))

The INDEX(DTable,0,4) bit determines/indicates the range to count: all rows of the 4th column of DTable.

0 means thus all rows;
4 means column D.

Additional examples:

=VLOOKUP(E1,DTable,2,0)

for ordinary lookup, where E1 houses a lookup value of interest.

=INDEX(DTable,MATCH(F1,INDEX(DTable,0,3),0),2)

would give you the date for F1 that matches a value in column 3 of DTable. That is, find the row in column 3 (column C) of DTable and the corresponding date from column 2 (column B) of DTable.

=MIN(INDEX(DTable,0,2))

will give you the earliest date that is entered in column 2 of DTable.

The above shows the advantage of the single name method for too many dynamic name ranges slows down a spreadsheet.

Aladin
This message was edited by Aladin Akyurek on 2002-10-03 15:50

 

[LoriD]

Thanks for everything Aladin. I appreciate the time you've taken to help me understand.
This is now working perfectly.