Naming a Cell

[tangledeleven]

I am trying to name a cell in an excel spreadsheet the same name as the FOLDER in which the spreadsheet is saved in.


I have done this for the DOCUMENT name by using: =MID(LEFT(CELL("filename",A1), FIND(".",CELL("filename",A1))-1),

 

[Hui]

Tangledeleven


Firstly Welcome to the Chandoo.org Forums


You cannot use formulas to assign a name to a Named Range

You can only use formulas as part of the Named ranges scope/range in the Refers To: box

 

[NARAYANK991]

Hi ,


Assume that a cell A1 contains :


E:Documents and SettingsAdministratorMy Documents[Book1.xlsm]Sheet1


Now , if you can isolate the folder name "My Documents" , by using a formula in cell A2 , then you can name a range by selecting A2 and doing it manually ( Create from Selection ) , or by using a VBA procedure similar to the following :


Public Sub Create_Range_Name()

ThisWorkbook.Worksheets("Sheet1").Range("A2").Select

Folder_name = Application.WorksheetFunction.Substitute(Selection.Text, " ", "_")

ThisWorkbook.Names.Add Name:=Folder_name, RefersTo:=Selection.Address

End Sub


Narayan