Thanks Terry I hear what you are saying about using the "Path" vs "Target" fields. As I am looking up a completely separate document, I fill in the "Path" field.
This is the formula which works if it finds "1216" but bombs out if 1216 had an embedded hyperlink: =IF(ISNA(VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0)); IF(ISNA(VLOOKUP(A8; Ravy.$J$4:$P$1010; 7; 0)); ""; VLOOKUP(A8; Ravy.$J$4:$P$1010; 7; 0)); VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0)) So, if looking up A8 (let's say "1216") gives us "N/A", then print null (blank field); If A8="1216" is found then look up the corresponding job number. It workd fine. However, if I select 1216 in the Jobs sheet and embed a hyperlink to the invoice JPEG, VLOOKUP can no longer find it and the recod disappears from my Invoice sheet. Regards, Jim ----- Original Message ----- From: "Terry" <[EMAIL PROTECTED]> To: <[email protected]> Sent: Thursday, September 07, 2006 4:36 PM Subject: Re: [users] Calc question.? > I've just tried, unsuccessfully, to replicate the issue. > > I have inserted a text hyperlink http://www.google.com.au with text > "google" in H8 and in I8 the formula =VLOOKUP(H8;A1:B28;2;0). The word > google is in a cell in column A and the formula correctly returns the > value in the corresponding cell in column B. > > I've tried the same thing with a link to the second sheet and text > "calendar". > > Is that different from what you are doing? > > Do you use Insert >Hyperlink? In the dialogue for "Document", there is > a box at the top for the path. If you are linking to a target in the > same document, you leave that blank and insert the target in the next > box down. In the second box from the bottom, insert the text which is > to be displayed and which you wish to use for VLOOKUP, in this case "1216". > > James Elliott wrote: >> I am using OOo 2.0.3 running under Windows XP >> >> In a Calc work book (is that what you call the three sheets tat appear >> by default: sheet#1, sheet#2, sheet#3?) I have renamed sheet#1 as >> "Jobs" and sheet#2 as "Invoices" >> >> On the Jobs sheet I have: >> ---job numbers from 1-1000 in column A >> ---customer names in column B >> ---Invoice number (if there is one *) in column C >> ---and other data in columns D-H >> >> (*) please note, the job numbers are in sequence but not every job has >> an invoice associated with it. For example, the job might be to issue a >> quote, and the quote is not accepted or proceeded with, therefore, no >> invoice. >> >> On the Invoices sheet I have: >> ---Invoice numbers from 1200 - 2000 in column A (current invoice numbers) >> ---Job numbers in Column B >> ---Customer name in Column C >> ---and other data in columns E-H >> >> To cut down on typing double entries I have used formulas in the Invoice >> sheet to fill in the name and job number if the nvoice number exists in >> sheet #1. >> >> In other words, if in the Jobs Sheet I have: >> Job W23 Kate Smith INV-1216 >> >> in the Invoice Sheet next to "1216" it will fill in the name and job >> number, like this: >> 1216 W23 Kate Smith >> >> NOW WE COME TO MY QUESTION !! - :o) >> >> In the Jobs Sheet, if I make the text "1216" a hyperlink so that I can >> click on it and bring up Invoice 1216 to have a look at, then all the >> information next to 1216 in the Invoice Sheet disappears (ie "W23 >> Kate Smith" disappears) ! ?? >> >> Is this because VLookup is looking for "1216" and the 1216-hyperlink is >> not the same as the text or number "1216"? >> >> If I substitute a simple formula such as: >> Invoice.B23 = Jobs.A23 >> That works! It doesn't matter if Jobs.A23 is text, or a number, or a >> hyperlink ... Invoice.B23 will show "1216" as its cell contents. >> >> When I use =IF( ) or VLookup( ) functions is when it ceases to work. > > -- > If you're seeking, check out http://www.rci.org.au > > Send instant messages to your online friends http://au.messenger.yahoo.com > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > > > > > -- > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.1.405 / Virus Database: 268.12.1/440 - Release Date: 6/09/2006 > >
