UID: 223236
帖子: 333
主题: 129
注册时间: 2011-7-11
最后登录: 2013-8-19

1^# 跳转到 » 倒序看帖

字体大小: tT

发表于 2011-10-12 00:16 | 只看该作者

Excel Question: Dynamic File names possible?

situation, different, possible, Windows, example

I have a sheet where I have many cells linking to many different excel files. I was wondering if it would be possible to create a formula where the file name changes depending on a cell. So for example:

Cell A1 has the word "Finance" in it.

Cell B1 pulls from C:\Windows\Program\"A1".xlsx

So in this situation it would be C:\Windows\Program\Finance.xlsx

If I change A1 to "Economics" then cell B1 will pull from C:\Windows\Program\Economics.xlsx

I've tried using & sings and concatenate but they don't seem to work correctly. I get extra quotation marks or something. Does anyone have any ideas?

himanshumh

CFA New Member

Rank: 1

UID: 223388
帖子: 226
主题: 41
注册时间: 2011-7-11
最后登录: 2014-7-3

2^#

发表于 2011-10-12 00:21 | 只看该作者

Basically, the problem I'm having with this is that the file name ends up like this:

'"C:\Windows\Program\Economics.xlsx"' when I use concatenate or &. I need to get rid of those stupid quotation marks so it just ends up like this:

'C:\Windows\Program\Economics.xlsx'

I think that will fix the problem, but I'm not sure. If anyone has any suggestions on how to fix this, or if there's another way to do it that will be great.

Thanks

TOP

yodacaia

CFA Candidates

Rank: 2

UID: 223454
帖子: 341
主题: 12
注册时间: 2011-7-11
最后登录: 2014-8-7

3^#

发表于 2011-10-12 00:32 | 只看该作者

You can use the indirect function to do this, take a look at the formula below

=INDIRECT("'"&"C:\Windows\Program\["&a1&".xlsx]Sheet1'!$G$8")

TOP

bodhisattva

CFA Candidates

Rank: 2

UID: 222301
帖子: 442
主题: 9
注册时间: 2011-7-2
最后登录: 2015-12-29

4^#

发表于 2011-10-12 00:38 | 只看该作者

Hey Kant,

You need to use the Indirect function to accomplish this; so there you would do:

Cell A1 = "ExcelFileName"

Cell B1 =INDIRECT("'"&"FilePath"&"["&$A$1&"]"&"TabName'!"&"Cell On Tab")

Now let me give you a real world example from one of my files:

Cell E35 = "Cost_Measures_08.11.xls"

Cell E36 = INDIRECT("'"&"C:\Users\453256\Desktop\NCR\"&"["&$E$35&"]"&"CM_Audits'!"&"C10")

Therefore, cell E36 will return the value of what is in cell C10 in the cost_measures file; if you change cell E35 to another file name that is in that folder, cell E36's value will be changed; hopefully this explains it.

TOP

comp_sci_kid

CFA Candidates

Rank: 2

UID: 223411
帖子: 318
主题: 17
注册时间: 2011-7-11
最后登录: 2016-4-19

5^#

发表于 2011-10-12 00:43 | 只看该作者

Thanks guys, but INDIRECT only works if you have all of the files you are accessing open. This is not possible in my case as there are many files I'm drawing the data from.

I want to avoid writing a macro for this because I just don't have the time right now. I can get to it later this evening, but using excel functions would just make things much easier.

Anyway, for some reason that I can't figure out right now, even if I have a file open INDIRECT is giving me a #REF error. When I click the Insert Function button to see what it's doing, the number it brings back is correct in the Ref_text box, but the result is showing the word Volatile and regardless of what I change in the A1 box, it still shows up as #REF. I'm still trying to play with this and see what the deal is.

Edited 1 time(s). Last edit at Tuesday, August 16, 2011 at 11:25AM by kant.

TOP

ruchita

CFA Candidates

Rank: 2

UID: 223358
帖子: 257
主题: 53
注册时间: 2011-7-11
最后登录: 2016-4-19

6^#

发表于 2011-10-12 00:49 | 只看该作者

You said, "INDIRECT only works if you have all of the files you are accessing open"

This is false, that's why you include the whole file path C:\Windows\Program\etc....

You should probably read some online tutorials regarding the INDIRECT function because we've essentially given you the exact formula to use and all you have to do is replace for your particular file.

TOP

xilinx_altera

CFA Candidates

Rank: 2

UID: 223426
帖子: 324
主题: 4
注册时间: 2011-7-11
最后登录: 2014-8-7

7^#

发表于 2011-10-12 01:00 | 只看该作者

Use INDIRECT

-------------------------------------------------------------------------------------

"Without American production the United Nations could never have won the war."
-Joseph Stalin, Tehran Conference: 1943

TOP

segalm