Is this possible

[pser]

Hi, Is this possible: in a combobox, in the attribute listfillrange to
link to another workbook like
'\\servername\share\folder\file.xls'Data!A2:B10

I have a combobox with listfillrange Data!A2:B10 refering to a hidden
sheet named data. But i want to move that sheet to a seperate file and
then link the data from that book.

Thanks
Paal

 

[Guest]

I have not tried it with a combo box, but it works when
used in a formula (I use it extesively with lookups)

='ServerDriveName:\FolderPath\[FileName.xls]WorkSheetName'!
$A$1:$A$10)

Hope this helps

Paul

 

[pser]

Nope, Won't accept if I type like you suggest. I've tried to search t
find what is allowed in that field bu no luck so far.

Thanks for the advice anyway...

Paa

 

[Dave Peterson]

I could do it if that second workbook were open.

 

[pser]

True, I can also do it if I type, let say
O:\foldername\file.xls'Data!A2:B10

But if i try to type
\\servername\sharename\foldername\file.xls'Data!A2:B10 it won't work.

Not everybody has that particular share connected to trhat drive so i
need to use \\servername\sharename, so question is since it doesn't
work, do you have other suggestion I would really appreciate it.

Thanks
Paal

 

[Dave Peterson]

Let excel help you build the formula.

Open both workbooks.

in a worksheet that will contain the formula, type = into a cell.
then point at a cell in the other workbook.

You'll see the correct syntax of the formula you need.

Close the workbook (file.xls).

If you opened file.xls from the O: drive, then go back and replace
0:
with the correct server/share name.

If you opened it using the UNC path, it might already be ok.

===
File|Open will accept the UNC stuff.

Just type
\\servername\sharename\foldername
and you'll be able to point and click on your file.

====
When I linked a single cell and closed the workbook, I got a formula that looked
like:

='C:\My Documents\excel\[book1.xls]Sheet1'!$A$1

Notice the square brackets and location of the single quotes.