File path

S

stew

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
 
D

David Biddulph

Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
 
S

stew

Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

David Biddulph said:
Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

stew said:
Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
 
D

Dave Peterson

If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)


Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

David Biddulph said:
Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

stew said:
Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 
S

stew

Hi dave

What I am trying to , I have now discoverd, does not require a Hyperlink
I am trying to replace a straight forward file path, shown below

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

With

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)&"Production Managers
Package\[Production Managers Spreadsheet.xls]Production Managers
Spreadsheet'!D111"

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25) gives me

C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\

So what am I doing wrong?

Best

Stewart



Dave Peterson said:
If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)


Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

David Biddulph said:
Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 
S

stew

..
Hi all

Ok Improvement but Still not sucessful

This is the file path I am trying to generate

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

I have now used

=("="&"'"&(MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)))&"Production
Managers Package"&"\"&"["&"Production Managers
Spreadsheet.xls"&"]"&"Production Managers Spreadsheet'!D111"

Which gives me

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

However it shows the formula and not the result

Can anybody help!!!

Best

Stewart

stew said:
Hi dave

What I am trying to , I have now discoverd, does not require a Hyperlink
I am trying to replace a straight forward file path, shown below

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

With

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)&"Production Managers
Package\[Production Managers Spreadsheet.xls]Production Managers
Spreadsheet'!D111"

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25) gives me

C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\

So what am I doing wrong?

Best

Stewart



Dave Peterson said:
If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)


Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

:

Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 
S

stew

Correction

the cell shows
='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111



stew said:
.
Hi all

Ok Improvement but Still not sucessful

This is the file path I am trying to generate

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

I have now used

=("="&"'"&(MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)))&"Production
Managers Package"&"\"&"["&"Production Managers
Spreadsheet.xls"&"]"&"Production Managers Spreadsheet'!D111"

Which gives me

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

However it shows the formula and not the result

Can anybody help!!!

Best

Stewart

stew said:
Hi dave

What I am trying to , I have now discoverd, does not require a Hyperlink
I am trying to replace a straight forward file path, shown below

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

With

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)&"Production Managers
Package\[Production Managers Spreadsheet.xls]Production Managers
Spreadsheet'!D111"

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25) gives me

C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\

So what am I doing wrong?

Best

Stewart



Dave Peterson said:
If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)



stew wrote:

Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

:

Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 
D

Dave Peterson

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.
.
Hi all

Ok Improvement but Still not sucessful

This is the file path I am trying to generate

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

I have now used

=("="&"'"&(MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)))&"Production
Managers Package"&"\"&"["&"Production Managers
Spreadsheet.xls"&"]"&"Production Managers Spreadsheet'!D111"

Which gives me

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

However it shows the formula and not the result

Can anybody help!!!

Best

Stewart

stew said:
Hi dave

What I am trying to , I have now discoverd, does not require a Hyperlink
I am trying to replace a straight forward file path, shown below

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

With

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)&"Production Managers
Package\[Production Managers Spreadsheet.xls]Production Managers
Spreadsheet'!D111"

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25) gives me

C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\

So what am I doing wrong?

Best

Stewart



Dave Peterson said:
If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)



stew wrote:

Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

:

Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 
S

stew

Dear Dave

Thank you

=INDIRECT.EXT("'"&LEFT(CELL("Filename",$A$1),SEARCH("[",CELL("filename",$A$1))-25)&"Road
Managers Package\[Tour Managers Spreadsheet.xls]Tour Managers
Spreadsheet'!"&ADDRESS(ROW()+101,(INT(COLUMN()/1))))

seems to have solved my problem

Best

Stew

Dave Peterson said:
The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.
.
Hi all

Ok Improvement but Still not sucessful

This is the file path I am trying to generate

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

I have now used

=("="&"'"&(MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)))&"Production
Managers Package"&"\"&"["&"Production Managers
Spreadsheet.xls"&"]"&"Production Managers Spreadsheet'!D111"

Which gives me

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

However it shows the formula and not the result

Can anybody help!!!

Best

Stewart

stew said:
Hi dave

What I am trying to , I have now discoverd, does not require a Hyperlink
I am trying to replace a straight forward file path, shown below

='C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\Production Managers Package\[Production Managers
Spreadsheet.xls]Production Managers Spreadsheet'!D111

With

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25)&"Production Managers
Package\[Production Managers Spreadsheet.xls]Production Managers
Spreadsheet'!D111"

=MID(CELL("filename"),1,FIND("[",CELL("filename"))-25) gives me

C:\Users\Welcome\Stewarts Files\Road accounting program\Road Accounting
Package\

So what am I doing wrong?

Best

Stewart



:

If you want to open the file to a specific worksheet, you're going to have to
include a specific cell, too (A1???).

Untested:

=HYPERLINK("file:\\\\"&MID(CELL("FILENAME",a1),1,
FIND("[",CELL("filename",a1))-10)
& "Road accounting program\Road Accounting Package"
& "\Road Managers Package\Tour managers Spreadsheet.xls]'"
& "Tour Managers Spreadsheet'!a1",D111)



stew wrote:

Hi David

I have changed that but other than that does the formula look right? Still
no success

Best

Stew

:

Usually safest to use CELL("FILENAME",A1) rather than CELL("FILENAME")
See Excel help for the CELL function.
--
David Biddulph

Hi All

The followig does not display the contents of D111.also it will not open
Tour Managers Spreadsheet , which is the sheet name inside Tour Managers
Spreadsheet Workbook.
In order to to eliminate the actual formula, can anybody see anything
physically wrong with the layout

=HYPERLINK(MID(CELL("FILENAME"),1,FIND("[",CELL("filename"))-10)&"Road
accounting program\Road Accounting Package\Road Managers Package\Tour
managers Spreadsheet.xls]'Tour Managers Spreadsheet'!",D111)

Thanks for looking

Stew
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

display contents of cell in another workbook 7
Copy and Paste Question 12
Locating cell using Hyperlink 10
Vlookup or Hlookup 7
ongoing hyperlink problem 3
ongoing question re hyperlink 3
Hyperlink 13
Inserting Field To Insert Full Path to Excel File 3

Top