OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Named Ranges

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
nomen
General User
General User


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

PostPosted: Mon Jan 21, 2008 11:06 am    Post subject: Named Ranges Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 21, 2008 11:44 am    Post subject: Reply with quote

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

=VLOOKUP(B2;Name;5;FALSE)
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 21, 2008 2:04 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
nomen
General User
General User


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

PostPosted: Tue Jan 22, 2008 5:22 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed Jan 23, 2008 6:54 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
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


Powered by phpBB © 2001, 2005 phpBB Group