You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@poi.apache.org by Lauren Riley <La...@FBLFinancial.com> on 2007/12/18 19:29:10 UTC
Using VLOOKUP
I am writing an excel file out...it has two cells with formulas. The first
one uses Index/Match to lookup a value in a table. The second cell needs
to use the "looked up" value in a VLOOKUP call (as the first parameter) to
another table. When the spreadsheet is written out I get this error in the
second cell, "A value used in the formula is of the wrong data type." And
it says #VALUE in the field. If I go to the second cell's function in the
textfield, and hit enter, then the correct value displays. So I know I
have the right formula, but it's like it can't compute the second cell
because it doesn't yet have the value from the first cell lookup. Is there
a way to get around this?
Thank you!
__________________________________________
The information contained in this message may be privileged and
confidential and protected from disclosure. If you are not the intended
recipient of this message, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us immediately
by replying to the message, and please delete it from your computer.
Re: Using VLOOKUP
Posted by Elvis Willems <ew...@schaubroeck.be>.
Hey Lauren,
I have had this same problem. The only solution I've found so far is to
store my template with OpenOffice Calc instead of MS Excel, in which
case the values show correctly when opening in either program. Although
I have seen some mails fly by, that gave me the impression you could do
something with FormulaEvaluator, but because I was pressured for time I
didn't try it out yet.
If you can get it to work this way, though, feel free to share.
Also, you can search the mailing list archives for more info.
Regards,
Elvis Willems
On Tue, 2007-12-18 at 12:29 -0600, Lauren Riley wrote:
> I am writing an excel file out...it has two cells with formulas. The first
> one uses Index/Match to lookup a value in a table. The second cell needs
> to use the "looked up" value in a VLOOKUP call (as the first parameter) to
> another table. When the spreadsheet is written out I get this error in the
> second cell, "A value used in the formula is of the wrong data type." And
> it says #VALUE in the field. If I go to the second cell's function in the
> textfield, and hit enter, then the correct value displays. So I know I
> have the right formula, but it's like it can't compute the second cell
> because it doesn't yet have the value from the first cell lookup. Is there
> a way to get around this?
>
> Thank you!
>
>
> __________________________________________
> The information contained in this message may be privileged and
> confidential and protected from disclosure. If you are not the intended
> recipient of this message, you are hereby notified that any dissemination,
> distribution, or copying of this communication is strictly prohibited. If
> you have received this communication in error, please notify us immediately
> by replying to the message, and please delete it from your computer.
**** DISCLAIMER ****
http://www.schaubroeck.be/maildisclaimer.htm
Re: Using VLOOKUP
Posted by ramilani12 <ra...@gmail.com>.
Hello Lauren
I have same problem
we can developer together VLOOKUP?
In my vacation I will try to develope VLOOKUP for POI.
But my sugestion for your problem: Can you to change layout sheet?
On Dec 27, 2007 5:35 PM, Lauren Riley <La...@fblfinancial.com> wrote:
> Ok, I really need VLOOKUP, INDEX, and MATCH...So I'm going to attempt to
> code them. Anybody have any helpful suggestions as to which one of the other
> functions I could look at that might be close for an example?
>
>
> [image: Inactive hide details for Manda Wilson <wi...@cbio.mskcc.org>]Manda
> Wilson <wi...@cbio.mskcc.org>
>
>
>
> *Manda Wilson <wi...@cbio.mskcc.org>*
>
> 12/19/2007 02:33 PM
> Please respond to
> "POI Users List" <us...@poi.apache.org>
>
>
> To
>
> "POI Users List" <us...@poi.apache.org>
> cc
>
>
> Subject
>
> Re: Using VLOOKUP
>
> The INDEX and MATCH functions have not been implemented yet, so your
> formula can't be evaluated.
>
> http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
> apache/poi/hssf/record/formula/functions/Index.java
> http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
> apache/poi/hssf/record/formula/functions/Match.java
>
> You can implement them yourself (and as Nick said about VLOOKUP, if
> you do send the implementations as a patch so that other people can
> use them :).
>
> Manda
>
> On Dec 19, 2007, at 2:28 PM, Lauren Riley wrote:
>
> > My cell value in bushelsPerFootCell is INDEX(Bushels!P13:AF67, MATCH
> > (" + widthCell + ",Bushels!O13:O67,1), MATCH(" + lengthCell +
> > ",Bushels!P12:AF12,1))
> >
> > HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
> > workbook);
> > CellReference cellReference = new CellReference(bushelsPerFootCell);
> >
> > HSSFRow evalBushelsPerFootRow = sheet.getRow(cellReference.getRow());
> > evaluator.setCurrentRow(evalBushelsPerFootRow);
> > HSSFCell evalBushelsPerFootCell = evalBushelsPerFootRow.getCell
> > (cellReference.getCol());
> > HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate
> > (evalBushelsPerFootCell);
> >
> > The cellValue has a cell type of 5 - an error. What's wrong with
> > what I have?
> >
> >
> > Nick Burch <ni...@torchbox.com>
> >
> >
> > Nick Burch <ni...@torchbox.com>
> > 12/19/2007 09:38 AM
> > Please respond to
> > "POI Users List" <us...@poi.apache.org>
> >
> > To
> >
> > POI Users List <us...@poi.apache.org>
> >
> > cc
> >
> >
> > Subject
> >
> > Re: Using VLOOKUP
> >
> >
> > On Tue, 18 Dec 2007, Lauren Riley wrote:
> > > If I go to the second cell's function in the textfield, and hit
> > enter,
> > > then the correct value displays. So I know I have the right
> > formula,
> > > but it's like it can't compute the second cell because it doesn't
> > yet
> > > have the value from the first cell lookup. Is there a way to get
> > around
> > > this?
> >
> > You could try using the formula evaluator to pre-compute the cell
> > types
> > and values:
> > http://poi.apache.org/hssf/eval.html
> >
> > Otherwise, if you know the type of the data that the cell will
> > hold, you
> > could try forcing the setting of that yourself
> >
> > Nick
> >
> > ---------------------------------------------------------------------
> > To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> > For additional commands, e-mail: user-help@poi.apache.org
> >
> >
> >
> >
> > __________________________________________
> > The information contained in this message may be privileged and
> > confidential and protected from disclosure. If you are not the
> > intended recipient of this message, you are hereby notified that
> > any dissemination, distribution, or copying of this communication
> > is strictly prohibited. If you have received this communication in
> > error, please notify us immediately by replying to the message, and
> > please delete it from your computer.
>
>
>
>
> __________________________________________
> The information contained in this message may be privileged and
> confidential and protected from disclosure. If you are not the intended
> recipient of this message, you are hereby notified that any dissemination,
> distribution, or copying of this communication is strictly prohibited. If
> you have received this communication in error, please notify us immediately
> by replying to the message, and please delete it from your computer.
>
--
raphael milani
:=]
Re: Using VLOOKUP
Posted by Lauren Riley <La...@FBLFinancial.com>.
Ok, I really need VLOOKUP, INDEX, and MATCH...So I'm going to attempt to
code them. Anybody have any helpful suggestions as to which one of the
other functions I could look at that might be close for an example?
Manda Wilson
<wilson@cbio.mskc
c.org> To
"POI Users List"
12/19/2007 02:33 <us...@poi.apache.org>
PM cc
Subject
Please respond to Re: Using VLOOKUP
"POI Users List"
<user@poi.apache.
org>
The INDEX and MATCH functions have not been implemented yet, so your
formula can't be evaluated.
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
apache/poi/hssf/record/formula/functions/Index.java
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
apache/poi/hssf/record/formula/functions/Match.java
You can implement them yourself (and as Nick said about VLOOKUP, if
you do send the implementations as a patch so that other people can
use them :).
Manda
On Dec 19, 2007, at 2:28 PM, Lauren Riley wrote:
> My cell value in bushelsPerFootCell is INDEX(Bushels!P13:AF67, MATCH
> (" + widthCell + ",Bushels!O13:O67,1), MATCH(" + lengthCell +
> ",Bushels!P12:AF12,1))
>
> HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
> workbook);
> CellReference cellReference = new CellReference(bushelsPerFootCell);
>
> HSSFRow evalBushelsPerFootRow = sheet.getRow(cellReference.getRow());
> evaluator.setCurrentRow(evalBushelsPerFootRow);
> HSSFCell evalBushelsPerFootCell = evalBushelsPerFootRow.getCell
> (cellReference.getCol());
> HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate
> (evalBushelsPerFootCell);
>
> The cellValue has a cell type of 5 - an error. What's wrong with
> what I have?
>
>
> Nick Burch <ni...@torchbox.com>
>
>
> Nick Burch <ni...@torchbox.com>
> 12/19/2007 09:38 AM
> Please respond to
> "POI Users List" <us...@poi.apache.org>
>
> To
>
> POI Users List <us...@poi.apache.org>
>
> cc
>
>
> Subject
>
> Re: Using VLOOKUP
>
>
> On Tue, 18 Dec 2007, Lauren Riley wrote:
> > If I go to the second cell's function in the textfield, and hit
> enter,
> > then the correct value displays. So I know I have the right
> formula,
> > but it's like it can't compute the second cell because it doesn't
> yet
> > have the value from the first cell lookup. Is there a way to get
> around
> > this?
>
> You could try using the formula evaluator to pre-compute the cell
> types
> and values:
> http://poi.apache.org/hssf/eval.html
>
> Otherwise, if you know the type of the data that the cell will
> hold, you
> could try forcing the setting of that yourself
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
> __________________________________________
> The information contained in this message may be privileged and
> confidential and protected from disclosure. If you are not the
> intended recipient of this message, you are hereby notified that
> any dissemination, distribution, or copying of this communication
> is strictly prohibited. If you have received this communication in
> error, please notify us immediately by replying to the message, and
> please delete it from your computer.
__________________________________________
The information contained in this message may be privileged and
confidential and protected from disclosure. If you are not the intended
recipient of this message, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us immediately
by replying to the message, and please delete it from your computer.
Re: Using VLOOKUP
Posted by Manda Wilson <wi...@cbio.mskcc.org>.
The INDEX and MATCH functions have not been implemented yet, so your
formula can't be evaluated.
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
apache/poi/hssf/record/formula/functions/Index.java
http://svn.apache.org/repos/asf/poi/trunk/src/scratchpad/src/org/
apache/poi/hssf/record/formula/functions/Match.java
You can implement them yourself (and as Nick said about VLOOKUP, if
you do send the implementations as a patch so that other people can
use them :).
Manda
On Dec 19, 2007, at 2:28 PM, Lauren Riley wrote:
> My cell value in bushelsPerFootCell is INDEX(Bushels!P13:AF67, MATCH
> (" + widthCell + ",Bushels!O13:O67,1), MATCH(" + lengthCell +
> ",Bushels!P12:AF12,1))
>
> HSSFFormulaEvaluator evaluator = new HSSFFormulaEvaluator(sheet,
> workbook);
> CellReference cellReference = new CellReference(bushelsPerFootCell);
>
> HSSFRow evalBushelsPerFootRow = sheet.getRow(cellReference.getRow());
> evaluator.setCurrentRow(evalBushelsPerFootRow);
> HSSFCell evalBushelsPerFootCell = evalBushelsPerFootRow.getCell
> (cellReference.getCol());
> HSSFFormulaEvaluator.CellValue cellValue = evaluator.evaluate
> (evalBushelsPerFootCell);
>
> The cellValue has a cell type of 5 - an error. What's wrong with
> what I have?
>
>
> Nick Burch <ni...@torchbox.com>
>
>
> Nick Burch <ni...@torchbox.com>
> 12/19/2007 09:38 AM
> Please respond to
> "POI Users List" <us...@poi.apache.org>
>
> To
>
> POI Users List <us...@poi.apache.org>
>
> cc
>
>
> Subject
>
> Re: Using VLOOKUP
>
>
> On Tue, 18 Dec 2007, Lauren Riley wrote:
> > If I go to the second cell's function in the textfield, and hit
> enter,
> > then the correct value displays. So I know I have the right
> formula,
> > but it's like it can't compute the second cell because it doesn't
> yet
> > have the value from the first cell lookup. Is there a way to get
> around
> > this?
>
> You could try using the formula evaluator to pre-compute the cell
> types
> and values:
> http://poi.apache.org/hssf/eval.html
>
> Otherwise, if you know the type of the data that the cell will
> hold, you
> could try forcing the setting of that yourself
>
> Nick
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
> For additional commands, e-mail: user-help@poi.apache.org
>
>
>
>
> __________________________________________
> The information contained in this message may be privileged and
> confidential and protected from disclosure. If you are not the
> intended recipient of this message, you are hereby notified that
> any dissemination, distribution, or copying of this communication
> is strictly prohibited. If you have received this communication in
> error, please notify us immediately by replying to the message, and
> please delete it from your computer.
Re: Using VLOOKUP
Posted by Lauren Riley <La...@FBLFinancial.com>.
My cell value in bushelsPerFootCell is INDEX(Bushels!P13:AF67, MATCH(" +
widthCell + ",Bushels!O13:O67,1), MATCH(" + lengthCell +
",Bushels!P12:AF12,1))
HSSFFormulaEvaluator evaluator = new
HSSFFormulaEvaluator(sheet, workbook);
CellReference cellReference = new
CellReference(bushelsPerFootCell);
HSSFRow evalBushelsPerFootRow =
sheet.getRow(cellReference.getRow());
evaluator.setCurrentRow(evalBushelsPerFootRow);
HSSFCell evalBushelsPerFootCell =
evalBushelsPerFootRow.getCell(cellReference.getCol());
HSSFFormulaEvaluator.CellValue cellValue =
evaluator.evaluate(evalBushelsPerFootCell);
The cellValue has a cell type of 5 - an error. What's wrong with what I
have?
Nick Burch
<nick@torchbox.co
m> To
POI Users List
12/19/2007 09:38 <us...@poi.apache.org>
AM cc
Subject
Please respond to Re: Using VLOOKUP
"POI Users List"
<user@poi.apache.
org>
On Tue, 18 Dec 2007, Lauren Riley wrote:
> If I go to the second cell's function in the textfield, and hit enter,
> then the correct value displays. So I know I have the right formula,
> but it's like it can't compute the second cell because it doesn't yet
> have the value from the first cell lookup. Is there a way to get around
> this?
You could try using the formula evaluator to pre-compute the cell types
and values:
http://poi.apache.org/hssf/eval.html
Otherwise, if you know the type of the data that the cell will hold, you
could try forcing the setting of that yourself
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org
__________________________________________
The information contained in this message may be privileged and
confidential and protected from disclosure. If you are not the intended
recipient of this message, you are hereby notified that any dissemination,
distribution, or copying of this communication is strictly prohibited. If
you have received this communication in error, please notify us immediately
by replying to the message, and please delete it from your computer.
Re: Using VLOOKUP
Posted by Nick Burch <ni...@torchbox.com>.
On Tue, 18 Dec 2007, Lauren Riley wrote:
> If I go to the second cell's function in the textfield, and hit enter,
> then the correct value displays. So I know I have the right formula,
> but it's like it can't compute the second cell because it doesn't yet
> have the value from the first cell lookup. Is there a way to get around
> this?
You could try using the formula evaluator to pre-compute the cell types
and values:
http://poi.apache.org/hssf/eval.html
Otherwise, if you know the type of the data that the cell will hold, you
could try forcing the setting of that yourself
Nick
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@poi.apache.org
For additional commands, e-mail: user-help@poi.apache.org