Update automatically name range once file is import

Hi All.

Is there a way where I can expand the name range automatically without the user have to set or adjust it manually?


Here is the logic, I have two files, FTE task list macro and task list file,each employee in a company has his own FTE tracker, the team lead will somehow update task list file and inform all employee that the updated task list is available in shared folder and they need to update their task list on their tracker, as you notice that the "Dept1" on FTE file is between range A3 to A107, on the task list that is updated it is range between a3 to 110. Now I have already set up a macro code where the user click the button and choose the task list file to copy it back to the fte tracker, the thing is on FTE tab, I used a drop down value to capture the range of Dept1,but since the range is not yet updated,not all in the list has been captured.

For elaborate example, you can download the 2 files and run the FTE macro file. I hope I explained well my problem. You can ask me if you still confused with my problem

PS: I have actual data for privacy purposes so I create a file that set as an example to my problem*supposed to be that there should be many dept). Task list tab supposed to be very hidden to the user before and after updates, I just set is visible for you to see the logic.

put the following formula in your data validation source section.

=OFFSET(INDIRECT("'Task list'!a"&MATCH($B$1;'Task list'!$B:$B;0));0;0;COUNTIF('Task list'!$B:$B;$B$1);1)

It still uses the cell B1 as the department selection.

Hi,

Without reading your whole question or looking at your attachments, this sounds like a job for Dynamic Named Ranges. Read:
http://www.bettersolutions.com/excel...G820716330.htm
http://tim.webanalyticsdemystified.com/?p=820