How to interpret a path to another workbook and use in formula bar
 

Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left

How to interpret a path to another workbook and use in formula bar
 

Take a look at GetOpenFileName. This will display the file browser dialog,
and the file can be selected. GetOpenFileName returns the full file path
which you can then work with.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"hals_left" wrote in message
om...
Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left

How to interpret a path to another workbook and use in formula bar
 

Something like:
A1: =INDIRECT("'"&A2&"[Book1.xls]Sheet1'!$A$1")

But I think the workbook needs to be open for it to work.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left

How to interpret a path to another workbook and use in formula bar
 

Thanks,

Is there no way to make it work without having the sheet open?

The method below works without the sheet open , but the indirect
function make thes path easily changable across many different cells.
Is there no way to combine the two approaches?

Thanks

='F:\[January_04.xls]Sheet1'!$L$92 + L91




"Rob van Gelder" wrote in message ...
Something like:
A1: =INDIRECT("'"&A2&"[Book1.xls]Sheet1'!$A$1")

But I think the workbook needs to be open for it to work.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left

How to interpret a path to another workbook and use in formula bar
 

I'm sorry. I don't know how to get that to work without leaving the workbook
open.

Another possibility would be to still use linked cells, but use A1 as a
pointer to whatever workbook.

eg.
A1: =OFFSET(B1, A2 - 1, 0)
A2: 1
B1: ='F:\[January_04.xls]Sheet1'!$L$92
B2: ='F:\[February_04.xls]Sheet1'!$L$92
B3: ='F:\[March_04.xls]Sheet1'!$L$92
B4: ...

Then just change 1 to 2 if you want February, 3 for March, etc...

I've seen some real messy solutions with linked workbooks and like to leave
it alone. My lack of experience shows here.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Thanks,

Is there no way to make it work without having the sheet open?

The method below works without the sheet open , but the indirect
function make thes path easily changable across many different cells.
Is there no way to combine the two approaches?

Thanks

='F:\[January_04.xls]Sheet1'!$L$92 + L91




"Rob van Gelder" wrote in message
...
Something like:
A1: =INDIRECT("'"&A2&"[Book1.xls]Sheet1'!$A$1")

But I think the workbook needs to be open for it to work.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left

How to interpret a path to another workbook and use in formula bar
 

Thanks Rob,
Just to update -
I have a fairly flexible solution now without using the INDIRECT,
instead using a custom vba sub to implement a multiple worksheet
replace function (replacing paths in the formula bar) and then saving
the replace string to a hiddden worksheet for future calls.

I also created a master workbook with 0 values that all new documents
link to by default. Its not the tidieest solution but it works with a
little disipline!

Thanks
hals_left


"Rob van Gelder" wrote in message ...
I'm sorry. I don't know how to get that to work without leaving the workbook
open.

Another possibility would be to still use linked cells, but use A1 as a
pointer to whatever workbook.

eg.
A1: =OFFSET(B1, A2 - 1, 0)
A2: 1
B1: ='F:\[January_04.xls]Sheet1'!$L$92
B2: ='F:\[February_04.xls]Sheet1'!$L$92
B3: ='F:\[March_04.xls]Sheet1'!$L$92
B4: ...

Then just change 1 to 2 if you want February, 3 for March, etc...

I've seen some real messy solutions with linked workbooks and like to leave
it alone. My lack of experience shows here.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Thanks,

Is there no way to make it work without having the sheet open?

The method below works without the sheet open , but the indirect
function make thes path easily changable across many different cells.
Is there no way to combine the two approaches?

Thanks

='F:\[January_04.xls]Sheet1'!$L$92 + L91




"Rob van Gelder" wrote in message
...
Something like:
A1: =INDIRECT("'"&A2&"[Book1.xls]Sheet1'!$A$1")

But I think the workbook needs to be open for it to work.

--
Rob van Gelder - http://www.vangelder.co.nz/excel


"hals_left" wrote in message
om...
Hi

How can I add values from a sheet in another workbook without
hardcoding the path in the formula e.g:
='F:\[January_04.xls]Sheet1'!$L$92 + L91


What I would like is to let the user select the workbook to pull
values from, by typing the path into another cell. I can do with vba
but its longwinded, is there a way to interpret the path from another
cell?

Thanks
hals_left