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

H

hals_left

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
 
B

Bob Phillips

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)
 
R

Rob van Gelder

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

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

hals_left

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 said:
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 said:
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
Click to expand...
Click to expand...
 
R

Rob van Gelder

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 said:
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" <[email protected]> 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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
H

hals_left

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 said:
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 said:
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" <[email protected]> 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


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
Click to expand...
Click to expand...
Click to expand...
 
Top