create defined name using variable file name in reference

  • Thread starter Thread starter hala
  • Start date Start date
H

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
 
Activeworkbook.Names.Add Name:="ACCPath", RefersTo:= "=" &
Activesheet.Range("U2").Value2
 
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
 
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

Otávio Alves Ribeiro said:
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

hala said:
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
 
Back
Top