NEED HELP!!!! Cell References a file location and a specific Tab.

[Johnny Thunder]

Hello Friends,


I have been stuck on this for a few hours now!

Cell A1= \\Wb\cp\apps\CPLMS\WBCP Financial Systems\PECOS\PECOS Cost Allocations\ACTUALS CK\2011\Cost Allocation Project\2011 OB PECOS DATA TEST.xls Which is a file location on my server

what I am trying to do is write this formula and instead of manually writing in the file location I wanted to designate a cell that has it so I can just put that cell in the formula.

** the problem I am running into is that once it links to the file I can't get it to link to a specific tab in the file? the tab name is "DCP001_Step1a"

I really need help with this so any suggestions will be appreciated.


This is the formula I am using and I would like to remove the file and path and just refernece cell A1 which I designate with the path info?

=SUMPRODUCT(--('\\Wb\cp\apps\CPLMS\WBCP Financial Systems\PECOS\PECOS Cost Allocations\ACTUALS CK\2011\Cost Allocation Project\2011 OB PECOS DATA TEST.xls'Data Dump'!$G$2:$G$3435=$C2),--('\\Wb\cp\apps\CPLMS\WBCP Financial Systems\PECOS\PECOS Cost Allocations\ACTUALS CK\2011\Cost Allocation Project\2011 OB PECOS DATA TEST.xls'Data Dump'!$J$2:$J$3435=$E2),--('\\Wb\cp\apps\CPLMS\WBCP Financial Systems\PECOS\PECOS Cost Allocations\ACTUALS CK\2011\Cost Allocation Project\2011 OB PECOS DATA TEST.xls'Data Dump'!K$2:$K$3435=$G2),'Data Dump'!$X$2:$X$3435)*$L2*0.77

Excel 2003

 

[Smitty]

You're looking for INDIRECT.

But it only works with open workbookd, so take a look at the Morefunc add-in. There's a feature called INDIRECT.EXT, which will work on closed workbooks.

HTH,