Named Ranges

[nomen]

I have built a formula with a link similar to this:

VLOOKUP(b12;'file:///path/name.ods'#$SheetX.$A$1:$F$1001;5;false)

I would like to use a named range rather than hardcode the specific cell addresses. I have tried substituting my range name immediately after the file name portion('), after the #, and after the $, but all fail.


Also, I would like to store the path, file name, and cell range
(preferrably as a range name) in a series of cells. For example,

Cell A2 = 'file:///path/
Cell A3 = name.ods
Cell A4 = [cell addresses or range name]

If I now build my formula as:

VLOOKUP(b12;A2&A3&A4;5;false)

I get an error. I tried wrapping the A2&A3&A4 portion in an INDIRECT function but that did not work either.


Can someone offer some help with these two problems?

Thanks.

[Villeroy]

Define the name in this document.
Name ='file:///path/name.ods'#$SheetX.$A$1:$F$1001

=VLOOKUP(B2;Name;5;FALSE)

[Villeroy]

Oh, I forgot: The following formula works with the other document's names:
=VLOOKUP(DDE("soffice";"C:\path\name.ods";"RangeName");5;false)
Mind the system notation of the path-name. There is an optional 4th argument. See help on DDE.

[nomen]

Thanks for your replies.

I tried your first suggestion and I had a concern about the range in the link file changing(mainly by the number of rows). If that happens, defining the range as you suggested first, does not adjust it in the main document.

Also, I use this same file in several other spreadsheets, so maintenance could be a nightmare when/if the range changes.

Unless there is a way to have the range name in the link, it looks like I will need to use your second idea. Am I correct in assuming that the parameters of the DDE command can be cell references? If so, that would probably solve both of my original questions.

[Villeroy]