Reference to closed workbook

[Francis Brown]

Hi

I have 600 or so workbooks with a piece of information in cell a6 that I
need to extract in another workbook. Each workbook is saved on a network G
drive under folder structure userdata\alpha\surname firstname -
userid\userdata.xls
where alpha is the letters A-Z based on surname and each user under their
respective alpha split folder has a foldername based on the following
"surname - firstname - userid"

I have a work book that in column A that has each userid listed and in
column B the users name in the format above "surname firstname"

How do I set a formula in column c that can use the items A and B to make up
a path reference to the appropriate userdat.xls workbook and set the info
from cell a6 in column c of the work listing the user names and id's

I know I can use left(B1, 1) to get the letter for the alpha slit but I can
seem to combine this with a concatenate into a path reference.

 

[Shane Devenshire]

Hi,

The best solution may be to use VBA, because the "best" solution isn't
available in the spreadsheet (that is INDIRECT) because your workbooks are
all closed.

Let us know what you think about using VBA

Cheers,
Shane Devenshire

 

[Francis Brown]

I used VBA and got it to work .

It turned out the file structure wasnt as rigid and the spreadsheet layouts
as consistent as I thought so I needed to use a number of if then else
structures to test for the diffrent posibilities but I got there in the end.