Hi Walter
You are correct when you asy "The first formula says if it is not on the
Jobs tab, look on the Ravy tab. If it is not there either, return ""
otherwise give the match from
Ravy. If it is on the Jobs tab, give the match from there". I didn't
mention the Ravy tab in my discussion as I didn't want to complicate the
issue - very asute of you to work that out.
It seems like I have done something silly and simple like trying to match
text to numbers. I will look at the problem again in the light of the
wisdom I have received from all of you.
Many thanks, James
----- Original Message -----
From: "Walter A. March" <[EMAIL PROTECTED]>
To: <users@openoffice.org>
Sent: Friday, September 08, 2006 10:14 AM
Subject: Re: [users] Calc question.?
Terry wrote:
Does your hyperlink include a text label? A8 should contain the
equivalent of =HYPERLINK("some jpeg";1216) or, if 1216 is a label,
"1216".
If you are using the hyperlink dialog, make sure that only 1216 is in for
Text. watch out for spaces or apostrophes (').
I'm surprised that I can't replicate your problem. See, what I think is
happening is that the hyperlink makes the number into text and vlookup, as
far as I know, doesn't match text to a number.
I think there's something wrong with your formula as posted (probably
double pasting):
=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))
should be :
=IF(ISNA(VLOOKUP(A8; Jobs.$J$4:$P$1010; 7; 0)); ""; VLOOKUP(A8;
Jobs.$J$4:$P$1010; 7; 0))
Depends on what you are trying to accomplish.
The first formula says if it is not on the Jobs tab, look on the Ravy tab.
If it is not there either, return "" otherwise give the match from Ravy.
If it is on the Jobs tab, give the match from there.
The second one just returns what is on Jobs.
WalterAM
Anyway, what are you doing working this late? (I've got no excuse except
for me it isn't work.)
Cheers (and good night)
James Elliott wrote:
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
