Cell Link with Variable (SharePoint)

[ltjackson]

First let me introduce myself before I start bugging you guys about an issue I'm having. My name is Lee and I work for a small company in northern Florida. I kinda got "voluntold" to build a report. I'm decent with excel and know my way around VBA as well so depending on the answers here I may or may not require the "idiot" version of it.

Okay so I've been dealing with this issue over the past couple of hours and I can't seem to find anything online at all. I am building a master sheet that will reference various workbooks and various sheets within each of these workbooks. These are all stored on SharePoint and I've had success in linking these manually but I'd like part of the formula to be a variable. The easiest way I can explain it is with a few snippets of formula and hopefully someone knows what I'm talking about.

I have the following formula that will pull data from cell I8 on one of the separate workbooks/sheets,

='[my spreadsheet.xlsm]sheet1'!$I$8

I would like to make this a variable, so for instance in cell A1 on the sheet that I'm building I would put in the words "my spreadsheet" and in A2 I would like to be able to put "sheet1" so my formula would work (in a perfect world) like this,

='"["&A1&".xlsm]"&"A2"&'!$I$8

Which obviously isn't working, any ideas?

Thanks ahead of time.

 

[ltjackson]

Bump