I have two tabs: tab1 is my game form and tab 2 is my player roster.
When I put in the team name on tab1 it populates the players from
tab 2.
This year the league mandated rosters have sequential numbering
of players on the game forms. The captains are in bold on tab 2.
When they appear on the ga
VLOOKUP and COUNTIF will not find a numeric search value in a list of text
cells. They will, however, find a text value in a list of numeric cells. Why
one way but not the other? I am not a fan of strong typing in end-user
applications (it's fine for programming languages, but not all spreadsheet
u
Hi,
It seems that vlookup function in calc can not deal with Chinese
characters. I hope someone here can help me verify it.
attached file:
http://ubuntuone.com/p/AFl/
Kind Regards,
xiang
-
To unsubscribe, e-mail: users-uns
NoOp wrote:
=VLOOKUP(B2,'file:///home///file1g.ods'#sheet1.$a$2:[.$I$25],8,0)
=VLOOKUP(SearchCriterion; Array; Index; SortOrder) is the form for the
function. The overall form seems a little off.
=VLOOKUP
(B2;'file:///home///file1g.ods'#sheet1.$a$2:$I$25;8;0)
Hope this helps.
--
Andy
OOo linux 3.1.0 (both standard OOo and Ubuntu OOo).
I'm trying to help someone troubleshoot a vlookup issue:
> I am working ubuntu 9.04 and OOo3.0.1
>
> While working on vlookup (two different files) I found the final
> result with #NAME? error after many combination I could not get the
> right o
Original Message
From: Johnny Rosenberg <[EMAIL PROTECTED]>
Date: Mon 20 Aug 2007 03:19:33 EST
> Or maybe you (Steve) mean that you don't want the #N/A to show up if
> the search string is not found in an unsorted range? In that case:
>
> =IF(A2="";"";IF(ISNA(VLOOKUPA2;$B$1:$C$1
Or maybe you (Steve) mean that you don't want the #N/A to show up if the
search string is not found in an unsorted range? In that case:
=IF(A2="";"";IF(ISNA(VLOOKUPA2;$B$1:$C$10;2;0));"";VLOOKUP(A2;$B$1:$C$10;2;0)))
This will probably be a bit slow, however, since in some cases the same
VLOOKUP w
On Sat August 18 2007 13:33, steve wrote:
> Hi,
> I use Ver2.2, is there a way to suppress the #N/A when the Vlookup is
> blank? Many thanks.
I test to see if the data input cell to test from is empty, as in
=IF(M252="";"";VLOOKUP(M252;$Chart.$A$9:$B$97;2))
(In this case, I enter a GL account num
Hi,
I use Ver2.2, is there a way to suppress the #N/A when the Vlookup is blank?
Many thanks.
--
Mr. S. Eva Hon.MRPS
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
Hi Warren,
I use MediaFire for posting files. There are many other sites out there
as well.
tc
Warren Griffin wrote:
I will try that, although the list is in order of the indicator, i.e. 1 through
7 in that order.
I'm trying to find a place I can put the file that others can have acces
Hello!
Check Help section about lookup function. I think the problem in your
case is that range should be in alphabetical order, otherwise nothing or
just something will work.
Andis
Warren Griffin wrote:
I will try that, although the list is in order of the indicator, i.e. 1 through
7 in t
I will try that, although the list is in order of the indicator, i.e. 1 through
7 in that order.
I'm trying to find a place I can put the file that others can have access to
it and see the equations/functions I used. Do you know any places where I
could post this and send out a shortcut?
Hi Warren,
Most attachments don't make it through to the list. I can take a guess
that you need to add a fourth parameter to your vlookup function. This
one tells the function that the list is not sorted. The default is a
sorted list.
= vlookup(a1;b5:c55;2,false)
If this is not the problem,
The problem is explained in the first sheet of the attached file, but I have
included a brief description below.
I am using a lookup table in one sheet in a logical statement in the next
sheet. The logical statement uses a value from the first sheet and the lookup
table to find a day of t
On 13:32 Fri 04 May , Joe Conner wrote:
> Just curious, what version of OpenOffice are you using, and what
> operating system?
>
>
Office 2.0 on Debian Etch. It's a standalone system and not connected to the
web.
--
It is often safer to be in chains than to be free.
Joseph K
Just curious, what version of OpenOffice are you using, and what
operating system?
JosephK wrote:
I have a woorkbook with 3 sheets.
Sheet1 holds a lookup table with item descriptions in column 1 and cost prices
in column 2.
Sheet2 holds pure data of invoices generated including items bought
I have a woorkbook with 3 sheets.
Sheet1 holds a lookup table with item descriptions in column 1 and cost prices
in column 2.
Sheet2 holds pure data of invoices generated including items bought
Sheet3 holds the analysis and takes the item description from sheet2, looks it
up in sheet1 and retur
Hi. It's the sort of formula that I at least try to avoid, but you
could just preface your VLOOKUP formulas with an IF statement:
IF(VLOOKUP()="";0;VLOOKUP(My bad - I double-checked and found that original cell is empty.
I can use it math formulas, but vlookup result generates error Err
Saturday, September 02, 2006 3:46 AM
> To: users@openoffice.org
> Subject: Re: [users] Vlookup of empty cell
>
> I checked that with ISNUMBER on the cell containing the
> VLOOKUP formula and got "TRUE". How do you reach the
> conclusion that the returned value is not a
It seems, finally, that the problem may arise from numbers entered as
text in the looked up range, rather than empty cells. Otherwise,
vlookup would return a numerical value if the cell looked up contained a
zero.
So, value/vlookup is the easiest answer.
That, of course, returns an error if
Of course. The original inquiry (see the subject heading) referred to
an empty cell. Hence my suggestion of index/match.
Vlookup also seems to return a numerical result if a zero is entered in
the looked up cell.
It seems that the problem arises from numbers entered as text.
Anthony Chilco
I checked that with ISNUMBER on the cell containing the VLOOKUP formula
and got "TRUE". How do you reach the conclusion that the returned value
is not a number?
Kirill S. Palagin wrote:
In my case cell is not empty - it contains the value of zero, I can use
that cell in all math operations, b
in a separate
cell, then reference that cell in the formula.
Ross
-Original Message-
From: Terry [mailto:[EMAIL PROTECTED]
Sent: Friday, September 01, 2006 5:03 PM
To: users@openoffice.org
Subject: Re: [users] Vlookup of empty cell
I've been trying to think of a reason for th
If there's a possibility of having empty cells, you have to add an
'iserror' test (or similar function) as well. It can get pretty messy
when dealing with unknown data sources.
tc
Terry wrote:
I tried that with a blank cell and got "Error:502". Yet it works on
your system?
Anthony Chilco w
I checked that with ISNUMBER on the cell containing the VLOOKUP formula
and got "TRUE". How do you reach the conclusion that the returned value
is not a number?
Kirill S. Palagin wrote:
In my case cell is not empty - it contains the value of zero, I can use
that cell in all math operations, b
I tried that with a blank cell and got "Error:502". Yet it works on
your system?
Anthony Chilco wrote:
Hi Krill,
Whenever I'm looking for numeric returns I enclose the function with
'Value(vlookup())'. Any alpha string returns will evaluate to zero.
Often, imported data will be interpre
That is even better - modifications to formulas are so much easier!
Thanks a lot!
WBR,
K. Palagin.
> -Original Message-
> From: Anthony Chilco [mailto:[EMAIL PROTECTED]
> Sent: Friday, September 01, 2006 6:25 PM
> To: users@openoffice.org
> Subject: Re: [users] Vlooku
rg
> Subject: Re: [users] Vlookup of empty cell
>
> I've been trying to think of a reason for the difference.
> Entering zeros in the range looked up would work. I'm not
> sure, if a cell is blank, that the result from VLOOKUP is wrong.
>
> Kirill S.
Hi Krill,
Whenever I'm looking for numeric returns I enclose the function with
'Value(vlookup())'. Any alpha string returns will evaluate to zero.
Often, imported data will be interpreted as strings, even though it
contains only numerals. The value function will convert these strings to
t
al Message-
From: Terry [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 31, 2006 2:20 PM
To: users@openoffice.org
Subject: Re: [users] Vlookup of empty cell
Use INDEX combined with MATCH as in:
=INDEX($B1:$B5;MATCH("alpha";$A$1:$A$5;0))
MATCH gets the row number in the reference ra
Yep, that did the trick.
Thanks a lot.
WBR,
K. Palagin.
> -Original Message-
> From: Terry [mailto:[EMAIL PROTECTED]
> Sent: Thursday, August 31, 2006 2:20 PM
> To: users@openoffice.org
> Subject: Re: [users] Vlookup of empty cell
>
> Use INDEX combined with MATCH
Use INDEX combined with MATCH as in:
=INDEX($B1:$B5;MATCH("alpha";$A$1:$A$5;0))
MATCH gets the row number in the reference range.
INDEX gets the value in the lookup range in that row.
Kirill S. Palagin wrote:
Hello.
I am hitting the problem where I need to return value of cell with
VLOOKUP a
Hello.
I am hitting the problem where I need to return value of cell with
VLOOKUP and if cell contains 0, result seems to be string, which can not
be used in math formulas. When I refer directly to cell (=Sale.D250) it
correctly returns zero.
Problem described clearly in last 3 comments to
http://
Sandeep Jain escribió:
Hi,
I am not able to put a vlookup in openoffice i want to pull some data
from a different sheet . Can you pls help me or let me know what is
the formula for the same ASAP.
The formula is vlookup.
Please, note that in openoffice you should use ";" between parameters,
n
Ross Johnson wrote:
Sandeep Jain wrote:
Hi,
I am not able to put a vlookup in openoffice i want to pull some data
from a different sheet . Can you pls help me or let me know what is
the formula for the same ASAP.
What have you tried so far? E.g. Formulas etc.
Hey... how about
=VLOOKU
Sandeep Jain wrote:
Hi,
I am not able to put a vlookup in openoffice i want to pull some data from a
different sheet . Can you pls help me or let me know what is the formula for
the same ASAP.
What have you tried so far? E.g. Formulas etc.
Ross
Hi,
I am not able to put a vlookup in openoffice i want to pull some data from a
different sheet . Can you pls help me or let me know what is the formula for
the same ASAP.
Thanks & Regards
Sandeep
Yeh, actually, I figured that syntax out. It's the Defined range part
that's driving me nuts.
Thanks!
On 10.07.2006, at 16:01, Paul wrote:
The following worked for me :
=VLOOKUP(A2;'file:///H:/price.ods'#$Sheet1.A2:B8;2;0)
However this is not using a defined range. Can't seem to find the
syn
The following worked for me :
=VLOOKUP(A2;'file:///H:/price.ods'#$Sheet1.A2:B8;2;0)
However this is not using a defined range. Can't seem to find the
syntax for a range... The help suggests that this should be able to be
done however.
/paul
On 7/11/06, Curtis Vaughan <[EMAIL PROTECTED]> wrote:
Can someone help me with the following syntax. Although maybe you
can't do this with VLOOKUP. I want to pull data from a different
spreadsheet.
So I have tried something like this:
=VLOOKUP(A3;"V1.ods";Vessels;1;0)
Where A3 is the reference in the sheet, V1.ods is the file in the
same dir
ah. That works a treat! thanks for the info on sorting too.
Christian
On Mon, 2005-08-29 at 10:50 -0400, Anthony Chilco wrote:
> Hi Christian,
> Sorry, the period was a typo. The formula should be:
>
> =IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2;false))
>
> Without the 'false
Hi Christian,
Sorry, the period was a typo. The formula should be:
=IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2;false))
Without the 'false' parameter, vlookup expects the list to be sorted.
That's why USD followed by SKR works. Having 'I' follow 'S' won't. If
you change the p
Hi Christian,
Please don't reply directly to anyone who answers your questions. Others
on the list may be able to help or may benefit from the thread.
For an exact match, add 'false' as the last parameter:
=IF($G44="";"";$H44/VLOOKUP($G44;$'currency rates'.$A$2:$B$5;2.false))
I'm assuming that
Hi Christian,
You're not specifying the last parameter of the vlookup function. It is
'true' or 'false', 'false' meaning to look for an exact match. By
default, it is 'true'.
tc
Christian de Larrinaga wrote:
I have a curious issue that looks like a bug with VLOOKUP.
I have a spreadsheet tha
Hi Christian,
Christian de Larrinaga <[EMAIL PROTECTED]> schrieb am Fri, 26 Aug 2005
10:48:37 +0100:
> I have a curious issue that looks like a bug with VLOOKUP.
>
> I have a spreadsheet that is a multi column list of expense items.
> In a support sheet I have a small two column table of curren
I have a curious issue that looks like a bug with VLOOKUP.
I have a spreadsheet that is a multi column list of expense items.
In a support sheet I have a small two column table of currency and
conversion rates.
I am using VLOOKUP to determine the currency code in the first
worksheetfor the expens
46 matches
Mail list logo
