create defined name using variable file name in reference

[hala]

please help in create defined name
EXAMPLe:
create name : ACCPath ---- Referes to (='E:\Data Folder\[externa work
book.xlsx]sheet1'!$K$8:$K$10000)
this works

I NEED to put (external work book.xlsx) in a cell and refere to it as
variable in the referes to (='E:\Data Folder\[&U2&]sheet1'!$K$8:$K$10000)
where U2 having the file name because it is changing meny times

 

[Bob Phillips]

Activeworkbook.Names.Add Name:="ACCPath", RefersTo:= "=" &
Activesheet.Range("U2").Value2

 

[Otávio Alves Ribeiro]

Hi there.
You may want to try:

Refers to: =''" & CELL("filename") & "sheet'1!$K$8:$K$10000

This name will be a valid name only after the workbook has been saved.

Regards,
Otávio

 

[Otávio Alves Ribeiro]

Just in time (I did not read your post that carefully ...)
You can use the following formula to get the name from another cell:

Refers to: ="'E:\Data Folder\[" & U2 & "]sheet1'!$K$8:$K$10000)

Regards,
Otávio

Hi there.
You may want to try:

Refers to: =''" & CELL("filename") & "sheet'1!$K$8:$K$10000

This name will be a valid name only after the workbook has been saved.

Regards,
Otávio

please help in create defined name
EXAMPLe:
create name : ACCPath ---- Referes to (='E:\Data Folder\[externa work
book.xlsx]sheet1'!$K$8:$K$10000)
this works

I NEED to put (external work book.xlsx) in a cell and refere to it as
variable in the referes to (='E:\Data Folder\[&U2&]sheet1'!$K$8:$K$10000)
where U2 having the file name because it is changing meny times