using excel cell as pointer in a directory path

[cotch]

I have a few files all with the same name for a few different members of staff, so I save them in different paths to keep them separate.

eg

c:\documents\data\jo blogs\excelfile.xls
c:\documents\data\fred smith\excelfile.xls
c:\documents\data\jane jones\excelfile.xls


cells is the sheet contain the persons name


jo blogs
fred smith
jane jones

I want to be able to use this for others in the organisation and make it a simple as possible

Is there a way to use the cell contents in the path name (without opening the sheet first)

I know I can point to each cell in the other file and excel will use it but there must be a way of making this happen more automated by using the cell contents

Craig

 

[wigi]

So, your task is to open the file called excelfile.xls based on the cell you click on?

 

[cotch]

Not quite, sorry



in cell a1 I have Joe Bloggs and I want to be able to retrieve data from the Joe Bloggs excelfile.xls to populate the sheet,
at the same time I want to retrieve data from the other members of staff to populate other tables in the same sheet

for instance hours worked for each, mileage traveled etc for each person in to one sheet.

 

[wigi]

In theory you could use the INDIRECT function. But that function requires the other source file to be opened.

I would suggest, since you only have a limited number of persons, to make 1 sheet for each person. And hide/show the sheet(s) you need. The file will be bigger than optimal, but that is less of an issue than fully dynamic links.

 

[cotch]

The three names are examples there is around 20 and if the staff change I want to be able to change the name in the sheet and create a new directory for them and the new data be used

INDIRECT would have worked but I dont want to have to open each sheet to reference it.

 

[wigi]

Then I have no immediate solution, maybe other helpers have some suggestions.