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

vlookup and cell address

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


Joined: 01 Sep 2006
Posts: 48

PostPosted: Mon Oct 30, 2006 6:39 am    Post subject: vlookup and cell address Reply with quote

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


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Oct 30, 2006 6:55 am    Post subject: Re: vlookup and cell address Reply with quote

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


Joined: 01 Sep 2006
Posts: 48

PostPosted: Mon Oct 30, 2006 7:10 am    Post subject: Reply with quote

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


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

PostPosted: Mon Oct 30, 2006 7:48 am    Post subject: Reply with quote

Does the MATCH function do the job?

Dewi
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 Oct 30, 2006 12:53 pm    Post subject: Reply with quote

Something like:
=HYPERLINK("#"& ADDRESS(MATCH(searchValue;searchVector;0);1))
where 1 stands for 1st column A
or with sheet name
=HYPERLINK("#Sheet1."& ADDRESS(MATCH(searchValue;searchVector;0);1))
with some display label:
=HYPERLINK("#Sheet1."& ADDRESS(MATCH(searchValue;searchVector;0);1);IF(ISNA(MATCH(searchValue;searchVector;0));"Not Found!";"Push Me"))
adding a cell-style
=HYPERLINK("#Sheet1."&ADDRESS(MATCH(searchValue;searchVector;0);1);IF(ISNA(MATCH(searchValue;searchVector;0));"Not Found!";"Push Me"))&T(STYLE("myHyperlink"))
where myHyperlink is some cell-style blue, italic, underline,...
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
David
Super User
Super User


Joined: 24 Oct 2003
Posts: 5668
Location: Canada

PostPosted: Mon Oct 30, 2006 1:35 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
eduzs
OOo Advocate
OOo Advocate


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

PostPosted: Mon Oct 30, 2006 3:35 pm    Post subject: Reply with quote

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


Joined: 01 Sep 2006
Posts: 48

PostPosted: Tue Oct 31, 2006 12:59 am    Post subject: Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 31, 2006 1:44 am    Post subject: Reply with quote

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


Joined: 01 Sep 2006
Posts: 48

PostPosted: Tue Oct 31, 2006 2:32 am    Post subject: Reply with quote

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


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Oct 31, 2006 3:15 am    Post subject: Reply with quote

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.
Having your list in Sheet1.A1:F1000
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)
Sheet2.A3: =ADDRESS(A$2;1) -> get address from row-number "Italy" and column #1
copied to right:
Sheet2.B3: =ADDRESS(B$2;1) --> get address from row-number 500 and column number 1
Sheet2.A4: =HYPERLINK("#"&A$3)
copied to right:
Sheet2.B4: =HYPERLINK("#"&B$3)

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("file:///path/file.ods#Sheet1.A1:B5")
HYPERLINK("#Sheet1.A1:B5") in this doc
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
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