Hi

I've got a table A1:Y147 where A1:Y1 are the field headings and D1 = "Student ID". What I want to be able to do is to name the range of cells from A1:Y*, where * is the last row where student ID data is found. This should be dynamic, constantly updating upon closing the file, so that the named range increases or decreases according to the increasing and decreasing size of the student id column.

Can anyone help? Thanks.

Try using the offset function. Sorry I don't have time to give you the
full function you need.

"XXL User" wrote:


Hi

I've got a table A1:Y147 where A1:Y1 are the field headings and D1 =
"Student ID". What I want to be able to do is to name the range of
cells from A1:Y*, where * is the last row where student ID data is
found. This should be dynamic, constantly updating upon closing the
file, so that the named range increases or decreases according to the
increasing and decreasing size of the student id column.

Can anyone help? Thanks.




--
XXL User

THIS METHOD ONLY WORKS IF THERE ARE NO BLANK ROWS IN YOUR LIST.

For simplicity I'll do it with a named value and a named range.

First create a new name "num_students". Use this formula for it:

=counta($D$2:$D$1000)

This returns the number of cells in the range of D2 to D1000 that
aren't blank. So it will give you the number of students in your list
assuming your data starts on row 2 and there are no blank values
anywhere until you get to the bottom of the list. I used D1000 as the
ultimate end of the sheet, you could pick something bigger if you have
more than 999 students.

Next create a new name "student_list". Use this forumula:

=OFFSET($A$2,0,0,num_students,25)

You now have a dynamic range that is determined by using A2 as the top
left value and moving across 25 colums to column Y and down
num_students to the last row of student information.

Hope that makes sense.


--
MattColeridge
------------------------------------------------------------------------
MattColeridge's Profile: http://www.excelforum.com/member.php...o&userid=37080
View this thread: http://www.excelforum.com/showthread...hreadid=568063