Direct Workbook reference

[snowmansmartie]

Hi guys,

I have 2 workbooks.

WB1 is stored in a Sub-Folder with a Username and stores a list of figures
WB2 is stored in the Root Folder and pulls information from WB1. For each User there is a separate Tab. I know by using =REPLACE(CELL("filename",A1),1,FIND("]",CELL("filename",A1)),"") will give me the Sheet Name.

What I would like to do is where I have a link - ='c:\scores\username\[Ticket Audit Form.xlsx]Week 1 Audit'!$B$130 - Where the username is I want to pull this from the sheet tab as they will be the same.

I have already tried to concatenate the replace formula in, but this doesn't work. Any help would be appreciated.

Cheers

 

[Chihiro]

Normally you'd use INDIRECT to use dynamic reference to sheet names etc. However, with closed workbook, INDIRECT will not work.

I'd recommend one of following methods.

1. ADODB Connection via VBA
2. MS Query connection (via data tab)
3. If you have access to PowerQuery, connection set up via PowerQuery along with Named ranges to pass parameter.

 

[snowmansmartie]

Thanks Chihiro, however Im not after HOW to return the data I can do this via ='c:\scores\username\[Ticket Audit Form.xlsx]Week 1 Audit'!$B$130 and this works a treat.

The issue I have is that there are multiple of these references on a single tab, and when I setup a new user (new tab in the workbook), I've got to go through the whole sheet and change these references to the New Users folder.

What I am looking for is a formula to change the above \username\ part to take the name of the tab as the tab and the folder name will be the same. This means I can setup a new Tab, and it will automatically point to the correct directory without me having to go through it.

 

[Chihiro]

Only way to change it en mass it via Find & Replace. Assuming folder path is the only variable, just copy one of the sheet and replicate. Then replace all instances with new reference to the folder.

I usually set up sheet containing user list and use that to pass connection string to ADODB and pull in info that way.