CODEWITHSUNDEEP
excelexcel-formulaexcel-2010
pufAmuf
pufAmuf

Reputation: 7805

Getting Reference (url link) from another cell?

I want to link a cell to an external file. The name of the external file changes daily though.

I created a cell that contains the full address of the external file (C:\Users\Main\Desktop\data\History\02092013.csv)

I then named that cell HistoryFileReference

So then why does this not work?

='HistoryFileReference'!$A$2

When I press enter, a window named "Update Values: HistoryFileReference" pops up, asking me to choose a file.

edit: I don't want to "update links" via excel. Thanks

Upvotes: 1

Views: 2329

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149325

For this to work you will have to manually put brackets in the file name and then store it as

C:\Users\Main\Desktop\data\History\[02092013.csv] in the cell which you have named as HistoryFileReference

Now open the CSV file and leave it open. Go back to the above sheet and type this formula

=INDIRECT("'" & HistoryFileReference & "02092013'!$A$2")

and press Enter and you are done :)

Caution: Next time you open the file, do not click on Update Links else all the values generated by the above formula will change into #REF! as Indirect doesn't work with close files.

For the sake of an example, I have place a csv file in C:\ as shown in the screenshot below and I am trying to retrieve values from Cell A1

enter image description here

Upvotes: 1

Related Questions