Indirect formula, using the servername

[Hein]

Hi all,

In 1 of my spreadsheets (made for others to use) I use the INDIRECT function:

Please Login or Register  to view this content.

In this case in F6 I have the directory path.
In F7 I have the Filename.
In F10 I have the Sheetname.
In Adress I have the cell I want to refer to.

My question concerns the directory path.
In my own case I refer to the "G:\" disk/server.
However, other people use a different letter for the same server.

Is it possible to use the actual name of the server, instead of the "G"?
The name of the server is:
(G: ) ICT-Services on 'ABCD1234'
(to prevent a smily to appear I have added an extra space between the ":" and the ")"

I have tried several combinations and added and/or left out different bits but it doesn't seem to work.

Is it at all possible, and if Yes, how can I do this?

Thanks in advance
Hein

[mubashir aziz]

My question concerns the directory path.
In my own case I refer to the "G:\" disk/server.
However, other people use a different letter for the same server.

Just use the complete directory path instead of G which is more feasible and me too sometime great headache because i these letters

[Hein]

Hi mubashir aziz.

Thanks for your reply, but ... how do I do that ?

I have tried, again, every possible variation with the name and/or '/" but I haven't managed to get it working.
Do you, or of course anybody else, have any other thoughts on how to do this??

Thanks
Hein



PS
Sorry for the delayed answer but we had our Whitsun - holidays so I've been away for a while.

[shg]

You could use a UNC path (\\server\path\file), but INDIRECT doesn't work with closed files, so I'm puzzled that you've gotten as far as your post suggests.

[JONvdHeyden]

INDIRECT.EXT may support this. It's available through the free addin called Morefunc: http://xcell05.free.fr/morefunc/english/

Although it means that each user that uses the workbook will need to have the addin installed.

[shg]

I believe you can configure morefunc to embed itself into a workbook without distributing the add-in.

[Hein]

Hi SHG
Thanks for your reply

You are right about the fact that the source file, when used in an INDIRECT function, has to be open.
In fact: the file is opened first, after this the target file is opened.

The target file gets embedded in SAP BW.
The source file (that holds budgets and Latest estimates) is located on a network disk.

The problem is that not everybody uses the same letter for this network disk. I use G:\, others use Z:\ or another letter.
The "shared" name is the actual name from this network disk (in this case: ICT-Services on 'ABCD1234'

Therefore I would like to use this name, instead of G:\

In other words: how can I change 'G:\Data\Hein\Excel\ (as used in the directorycel F6) to 'ICT-Services on 'ABCD1234':\Data\Hein\Excel\

Hope you know what to do?

Thanks in advance
Hein

[shg]

I'm not sure if this answers the mail, but this function will return the UNC path to a file given the mapped drive path:

Please Login or Register  to view this content.

Stormseed, please start your own thread if you have a question.

[Hein]

Hi SHG
Thanks for the trouble you went through to make this code!
Unfortunately I don't have time now to get through it
I won't be back until thursday, so then I will get into the code

Sorry for not being able to get right into it now
I'll get back to you Thursday
Hein