Using Cell References as variables to index(match) in files

[jocoop]

Hello All,
I have recently incoroprated the index(match) to pull from reports on the server. The formula works very well, but due to the sheer number of applications, I would like to take my index(match) formula, stick variables in for Workbook Name and Tab Name, and then just paste the formula anywhere I want, and change reference cells. I tried to concatenate it, but it put the file path in quotes. Here is what I am working with:

=INDEX('\\Sdc-svr1\staffplan\[Customer Staff Plan.xls]Customer Detail'!$A$1:$CN$1000,MATCH($C24,'\\Sdc-svr1\staffplan\[Customer Staff Plan.xls]Customer Detail'!$A$1:$A$1000,0),MATCH(K$2,'\\Sdc-svr1\staffplan\[Customer Staff Plan.xls]Customer Detail'!$A$5:$DD$5,0))

So what I wanted to do was this:

=INDEX('\\Sdc-svr1\staffplan\[CELL A1]CELL A2'!$A$1:$CN$1000,MATCH($C24,'\\Sdc-svr1\staffplan\[CELL A1]Cell A2'!$A$1:$A$1000,0),MATCH(K$2,'\\Sdc-svr1\staffplan\[CELL A1]CELL A2'!$A$5:$DD$5,0))

The other caveat is that I don't want to use any INDIRECT. I previously built an indirect report puller, and disliked having to open everything. If it can be done, it will open up a lot of doors for building reports for me. Thanks in advance!

 

[ravishankar]

Hi
I think a custom function can help you
in that function you can define all the variables you want and call that function from the worksheet.
ravi