I am trying to define a dynamic named range in Excel 2000 & 2003
The format of the column I am referencing is:
Header
(blank row)
Data 1
Data 2
Data 3
ect...
The column is formatted as general and the data can be either text or numeric. The range needs to include row 2 (the blank) down to the last filled row.
I currently have the range set as =OFFSET($A$2,0,0,COUNTA($A:$A),1)
This works fine unless I start with only the header and blank row 2 and then add something to row 3. In this case the range fails to expand to include the data. It just references the blank row.
I tried =OFFSET($A$2,0,0,MAX(($A:$A<>"")*(ROW($A:$A)))-1, 1) that I found on another forum. It appears to be valid in the Name box, but when I try and reference the range in code I get an error. I also can't get it to box the cells on the sheet when I am in the name box. The first formula draws a blue box around the referenced cells.
Any help?
The format of the column I am referencing is:
Header
(blank row)
Data 1
Data 2
Data 3
ect...
The column is formatted as general and the data can be either text or numeric. The range needs to include row 2 (the blank) down to the last filled row.
I currently have the range set as =OFFSET($A$2,0,0,COUNTA($A:$A),1)
This works fine unless I start with only the header and blank row 2 and then add something to row 3. In this case the range fails to expand to include the data. It just references the blank row.
I tried =OFFSET($A$2,0,0,MAX(($A:$A<>"")*(ROW($A:$A)))-1, 1) that I found on another forum. It appears to be valid in the Name box, but when I try and reference the range in code I get an error. I also can't get it to box the cells on the sheet when I am in the name box. The first formula draws a blue box around the referenced cells.
Any help?