[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

Author Message
nomen
General User

Joined: 29 Oct 2007
Posts: 26
Location: Ossian, Indiana

 Posted: Mon Jan 21, 2008 11:06 am    Post subject: Named Ranges 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
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Mon Jan 21, 2008 11:44 am    Post subject: Define the name in this document. Name ='file:///path/name.ods'#\$SheetX.\$A\$1:\$F\$1001 =VLOOKUP(B2;Name;5;FALSE)
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

 Posted: Mon Jan 21, 2008 2:04 pm    Post subject: 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
General User

Joined: 29 Oct 2007
Posts: 26
Location: Ossian, Indiana

 Posted: Tue Jan 22, 2008 5:22 am    Post subject: 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
Super User

Joined: 04 Oct 2004
Posts: 10081
Location: Germany

Posted: Wed Jan 23, 2008 6:54 am    Post subject:

 Quote: 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.

Open two spreadsheets and give a try. DDE takes 3 string arguments and one optional number. First argument always is "soffice". At least I have never seen an example where another "DDE server" has been used successfully. If the string is a literal "soffice" or a reference to a cell having the string value "soffice" makes no difference.
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum