Variables within File Name References

dariuzthepole

Board Regular
Joined
Jul 23, 2008
Messages
111
Hi,

I'm looking to reference the same cell in multiple other workbooks and I'm trying to add in a variable with the user's name, to make it more resilient.

What I have is this:
='C:\Documents and Settings\john.smith\Desktop\[2013 John Smith.xlsm]Sheet1'!$A$2

'John Smith' is entered in A1 also, so I'm looking to replace 'John Smith.xlsm' with something like '\[2013 &A1&.xlsm]...'

I just cannot seem to get the syntax correct! I'm sure it is easy enough. I'm using WinXP and Excel 2010.

Any help you could provide would be greatly appreciated!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
Try

Code:
= "C:\Documents and Settings\john.smith\Desktop\[2013 " & Range("A1").Value & ".xlsm]Sheet1'!$A$2"
 
Upvote 0
Code:
[B]=INDIRECT("'C:\Documents and Settings\john.smith\Desktop\[2013" &  A1 & ".xlsm]Sheet1'!$A$2")[/B]
 
Upvote 0
Sorry but I can't get either of the above methods to work, both resulting in a #REF! error. This is being entered directly in a cell, not in VBA modules.

Thanks for your assistance.
 
Upvote 0
Maybe

= "C:\Documents and Settings\john.smith\Desktop\[2013 " & A1 & ".xlsm]'Sheet1'!$A$2"
 
Upvote 0
Then maybe


=INDIRECT("C:\Documents and Settings\john.smith\Desktop\[2013 " & A1 & ".xlsm]'Sheet1'!$A$2")

The file will have to be open for this to work.
 
Upvote 0
Then maybe


=INDIRECT("C:\Documents and Settings\john.smith\Desktop\[2013 " & A1 & ".xlsm]'Sheet1'!$A$2")

The file will have to be open for this to work.

Is there a method to perform this function without opening the file? I've got dozens of files and I do not want to be opening every file every time.

Thanks.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top