how get network location, not drive name for CELL?

I

Ian Elliott

Thanks for any help.
I have a cell at the bottom of my worksheet that shows where the file is
located, by using =CELL("filename"). This way, other users who see the
print-out can easily find the file. But different users in our company map
different folders to their drives. So my M: drive might be \\PRXHIX\WORK\,
but another's might be \\PRXHIX\SHARED\ (not the real folder names). To get
around this, I did:
="\\PRXHIX\WORK\" & RIGHT(CELL("filename"),LEN(CELL("filename"))-2)
but if some other user takes this file, they have to rewrite the beginning
if their mapped drive is different. Is there some function in Excel that
gives the folder, not just the drive mapping?
Thanks again.
 
D

Dave Peterson

First, you should use:
=cell("filename",a1)
without that cell reference, you'll be getting the last workbook name when excel
recalculates.

But if you drop the mapped drive and used the UNC path, then it should be
showing that UNC path--and that doesn't care about mapped drives.
 
I

Ian Elliott

Thanks Dave, I put in the a1, but how do I drop the mapped and use the UNC
path?
-Ian
 
D

Dave Peterson

Didn't your formula do that:

="\\PRXHIX\WORK\" & RIGHT(CELL("filename",a1),LEN(CELL("filename",a1))-2)

Although, I don't think you need the trailing backslash after work\:

And I may have checked to make sure it wasn't already the UNC path:

=IF(MID(CELL("Filename",A1),2,1)=":",
"\\PRXHIX\WORK"&RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-2),
CELL("filename",A1))

(all one cell)
 
I

Ian Elliott

Thanks Dave, you're right, it did.
But if I can get away with writing the "\\PRXHIX\WORK\" in there, I would
like that too, in the rare chance that it gets changed (or the more likely
chance the file gets moved). I did a search, and found a function that gets
the UNC path, it's just a little long (20 lines or so).
Thanks-Ian
 
D

Dave Peterson

Once you get the code working, it won't matter how long it is.

Ian said:
Thanks Dave, you're right, it did.
But if I can get away with writing the "\\PRXHIX\WORK\" in there, I would
like that too, in the rare chance that it gets changed (or the more likely
chance the file gets moved). I did a search, and found a function that gets
the UNC path, it's just a little long (20 lines or so).
Thanks-Ian
Click to expand...
 
Top