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

Author Message
General User

Joined: 01 Sep 2006
Posts: 48

 Posted: Mon Oct 30, 2006 6:39 am    Post subject: vlookup and cell address How can i obtain the cell address of the cell found by using the function VLOOKUP ? VLOOKUP returns a value that it finds in a table. I need to know the cell address too so that i can jump to it (like a cat). Can anyone help ? Thanks.
David
Super User

Joined: 24 Oct 2003
Posts: 5668

 Posted: Mon Oct 30, 2006 6:55 am    Post subject: Re: vlookup and cell address Sorry, no answer here, but my curiosity [and lack of understanding] begs another question: Why? You will already now have the contents of that cell copied in the cell containing the lookup function. If you have the cell address, it's just a pointer to that lookup cell, and you'd likely be needing that for some reason such as finding the contents ...which you already have. I'm wondering then what other purpose? David.
General User

Joined: 01 Sep 2006
Posts: 48

 Posted: Mon Oct 30, 2006 7:10 am    Post subject: I have an enormous table (A10:Z1000) organised in blocks. e.g. row a10:p21 is a block (or a table inside a table) All blocks do not have the same number of rows. These blocks contain numbers , texts, formulas and formatted cells. This big table is continously adjourned by adding new blocks. In col A are the values that the function VLOOKUP returns. These values are progressive numbers (1, 2, 3 etc. ) that do not coincide with the row numbers of CALC. The value that VLOOKUP returns identifies a block. So to see the block I need to push PagDn key several , several times befor i get to the block. I copy the block and paste it where i require it. I hope this explains. Better solutions are accepted.
DewiCorn
Newbie

Joined: 04 Jul 2006
Posts: 2
Location: Ynys Mon

 Posted: Mon Oct 30, 2006 7:48 am    Post subject: Does the MATCH function do the job? Dewi
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

David
Super User

Joined: 24 Oct 2003
Posts: 5668

Posted: Mon Oct 30, 2006 1:35 pm    Post subject:

 pradhan wrote: The value that VLOOKUP returns identifies a block.

By name? If you have the blocks of cells named, you can get a drop-down listing of those names in the window top left that shows the current cell number. That is, if vlookup shows a text name, and you find that name in the drop-down listing of the names you have created for blocks of cells, you can jump to it from there.

David.
eduzs

Joined: 07 Feb 2005
Posts: 356
Location: RJ, BRAZIL

 Posted: Mon Oct 30, 2006 3:35 pm    Post subject: Instead of VLOOKUP, if you can, use =CORRESP("FindMe";A3:A10;0) will give you the line number of "FindMe" in the search area, so you can use =INDIRECT("A"+X) to refer to other cells in relative position, for example INDIRECT("A3") = A3, X can be the result of CORRESP function cell, now you can play to see if you find what you want. I don't knbow the exact function name in english, but these functions is in the group of spreadsheet functions.
General User

Joined: 01 Sep 2006
Posts: 48

Posted: Tue Oct 31, 2006 12:59 am    Post subject:

 Villeroy wrote: Something like: =HYPERLINK("#"& ADDRESS(MATCH(searchValue;searchVector;0);1)) where 1 stands for 1st column A

Thanks a lot Villeroy.

I have tried the hyperlink function as above quote and workes fine. Just as I wanted. I'm trying to out the styles to create a button though I'm unable to translate ISNA function in Italian language.

Is it possible to configure OOOcalc to work in two languages (English and Italian)?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

 Posted: Tue Oct 31, 2006 1:44 am    Post subject: #NA in english is that kind of error you get with =MATCH("nonsense";A1:A2;0) The formula is syntactically correct and there are no errors in A1:A2, but the desired value "nonsense" is simply not there. This error is somehow "weaker" than the others, so it has it's own function and you can generate it with =NA(). ISNA() -->TRUE if #NA ISERROR() --> TRUE if any error ISERR() -->TRUE if any error, except NA() Try a correct match() on non-existing value and/or use the formula-wizzard (Ctrl+F2) and have a look at function-category "Information". Second question: If you like to use any other language(s) with openoffice.org, there are language-packs availlable. http://www.oooforum.org/forum/viewtopic.phtml?t=45386&highlight=language+pack Linux-distributions have them integrated in their packaging system, on Windows you have to do the usual download-and-exe. If formula translation is your only problem: Have a look at my below. Copy the code, Menu:Tools>Macros>Organize Browse to Container:"My Macros">Library"Standard", button "New", accept default module name and replace the existing code "Sub Main...End Sub" with the code in your clipboard. Call with Tools>Macros>Execute... or customize it. If my OOo runs in german mode, I call this with Ctrl+Shift+F1 (see Tools>Customize)._________________Rest in peace, oooforum.org Get help on https://forum.openoffice.org
General User

Joined: 01 Sep 2006
Posts: 48

 Posted: Tue Oct 31, 2006 2:32 am    Post subject: The hyperlink becomes inactive when the SearchValue changes. My search value is VLOOKUP(). Though it does not happen all the time. It happens after VLOOKUP() returns #NA value. Is it nornal ?
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

Posted: Tue Oct 31, 2006 3:15 am    Post subject:

 pradhan wrote: The hyperlink becomes inactive when the SearchValue changes. My search value is VLOOKUP(). Though it does not happen all the time. It happens after VLOOKUP() returns #NA value. Is it nornal ?

Yes it's normal. Call help on ADDRESS() function and split up things for better understanding.
Please fill some cells with resonable values:
Sheet2.A1: pradhan (an existing value to be looked up in first column of the list)
Sheet2.A2: =VLOOKUP(\$A\$1;\$Sheet1.\$A\$1:\$F\$1000;2;0) --> we may get "Italy" for instance
Sheet2.B2: =MATCH(\$A\$1;\$Sheet1.\$A\$1:\$A\$1000;0) --> we may get 500 (the position within A1:A1000)
copied to right:
Sheet2.B3: =ADDRESS(B\$2;1) --> get address from row-number 500 and column number 1
copied to right:

ADDRESS(rowID,columnID) takes at least two *numbers* and returnes the address of the cell, specified by it's row and column.
The preceeding "#" indicates a position within *this document*
VLOOKUP returns the *content* of a cell, which may be anything.

HYPERLINK("file:///path/file.txt") tries to open an ordinary file.
HYPERLINK("file:///path/file.odt#Bookmark") The writer or html document has defined some position named "Bookmark": try open the file and jump to "Bookmark".
In Calc, any cell-address is a valid bookmark.
HYPERLINK("#A1:B5") in this doc, this sheet

How I got here, scrolled to the position of your last posting:
http://www.oooforum.org/forum/viewtopic.phtml?p=184565#184565
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
 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