• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Include new line in my named range

kevinonearth

kevinonearth

Member
Hi there,
I've got a named range, a list of employees, it includes their title, name, address, employee number, contract start and contract finish date. It contains say 52 employees.
I've named my list Emp_list
But if I add a new employee to my list (number 53), how do I insure that they are automatically included in my named range?
I do not want to have to go in to the name manager every time to expand the named range!
Is there a way to set it so that excel sees I've added an extra line and it just automtically includes the new line into my Emp_list?
Thanks
 
Hello Kevin,
You can set your Emp_List to a formula like below:
=$A$1:INDEX($A:$A,MATCH(REPT("z",99),$A:$A))

This assumes that your list starts on A1, and is in column A.

Cheers,
Sajan.
 
Good day kevinonearth

Why do you not just turn your data into table and use it as a named range, then when every you add a row by having the cursor in the bottom right cell of the table and hitting the tab key a new row will be added to the table and the named range, there is no need to keep changing the cell references in the name manager.
 
Hi, kevinonearth!
You could define a dynamic named range too. Asumming that your data starts at cell A1 with row 1 of titles and that there isn't anything else below or at the right (it there's something you can still use a couple of workarounds), it should be like this:
EmployeeTable: =OFFSET($A$2,,,COUNTA($A:$A)-1,COUNTA($1:$1))
Regards!
 
Hi SirJB7,
Since COUNTA counts the values while excluding empty cells, it may not be reliable if there are blank rows for any reason.

-Sajan.
 
Hi, Sajan!
Asumming that it was the employee table, and that at 1st column it should be an ID or an unique identifier, even if you're theoretically right, I'd still stick to that definition.
Regards!
 
Hi SirJB7,
It is not just a theoretical limitation... COUNTA's functionality is clearly documented in the Help. COUNTA counts cells, and the count does not include blanks. Folks need to be aware of that. That is all.

-Sajan.
 
You must log in or register to reply here.
Back
Top