Here's my problem. I need to figure out how to nest a HLookUp function
within an IF function. If the hlookup finds the number in a seperate
worksheet, then I need it to display that in the cell, otherwise, if
the hlookup does not find the number, I just need a blank space.

I can't use the ISERROR function because of all the additional code
that would need inputting. I was hoping that I could figure out a way
to assign the HLookUp formula to a variable to reduce the calculation
time as well as the code size. Here's an example:

IF(a,a,"")

which would be this:

IF(HLOOKUP("Order",TrackingGantt!$A:$Z,MATCH($J20, TrackingGantt!$B:$B,FALSE),FALSE),HLOOKUP("Order", TrackingGantt!$A:$Z,MATCH($J20,TrackingGantt!$B:$B ,FALSE),FALSE),"")

or something similar.

Thanks


--
jesahs
------------------------------------------------------------------------
jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687
View this thread: http://www.excelforum.com/showthread...hreadid=503494

Why cant you simply use

IF(isna(HLOOKUP("Order",TrackingGantt!$A:$Z,MATCH( $J20,TrackingGantt!$B:$B,FALSE),FALSE)),"",HLOOKUP ("Order",TrackingGantt!$A:$Z,MATCH($J20,TrackingGa ntt!$B:$B,FALSE),FALSE))


"jesahs" wrote:


Here's my problem. I need to figure out how to nest a HLookUp function
within an IF function. If the hlookup finds the number in a seperate
worksheet, then I need it to display that in the cell, otherwise, if
the hlookup does not find the number, I just need a blank space.

I can't use the ISERROR function because of all the additional code
that would need inputting. I was hoping that I could figure out a way
to assign the HLookUp formula to a variable to reduce the calculation
time as well as the code size. Here's an example:

IF(a,a,"")

which would be this:

IF(HLOOKUP("Order",TrackingGantt!$A:$Z,MATCH($J20, TrackingGantt!$B:$B,FALSE),FALSE),HLOOKUP("Order", TrackingGantt!$A:$Z,MATCH($J20,TrackingGantt!$B:$B ,FALSE),FALSE),"")

or something similar.

Thanks


--
jesahs
------------------------------------------------------------------------
jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687
View this thread: http://www.excelforum.com/showthread...hreadid=503494

The reason I can't go that route, is because my boss says that it will
increase the loading time for the spreadsheet. He wants a minimal
amount of code for a minimal amount of computing.

I reading about Conditional Formatting. Seems like I could just use
this to make all errors defult to just a white cell. Is that a
possibility?


--
jesahs
------------------------------------------------------------------------
jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687
View this thread: http://www.excelforum.com/showthread...hreadid=503494

I left out an important detail. The main purpose is to remove any
*errors *that may appear on this sheet.

ISERROR and ISNA are not options. The fact that the already long
formulas are repeted twice causes an increase in calculation time
according to my boss.

What I'd like to do is assign a variable to my formula then use it in
an if statement. An example is shown in my original post above.

I'm looking at using a possible Conditional Format, but haven't gotten
it to work yet.

Thanks.


--
jesahs
------------------------------------------------------------------------
jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687
View this thread: http://www.excelforum.com/showthread...hreadid=503494

I suppose if your spreadsheet is huge, or if you have an old, slow PC, then
your boss' concern might be valid. However, if you have a reasonably recent
PC with 256 megs or RAM or more then the processing time is going to be
insignificant - 10 or 20 seconds to maybe a minute.

Any other solution is going to have the same issue - you have to trap for
errors and that means repeating the formula.

You cannot get around it by assigning the formula to a name (i.e.,
variable). If the formula generates an error in a cell - it will do so in
the name, too.




"jesahs" wrote:


I left out an important detail. The main purpose is to remove any
*errors *that may appear on this sheet.

ISERROR and ISNA are not options. The fact that the already long
formulas are repeted twice causes an increase in calculation time
according to my boss.

What I'd like to do is assign a variable to my formula then use it in
an if statement. An example is shown in my original post above.

I'm looking at using a possible Conditional Format, but haven't gotten
it to work yet.

Thanks.


--
jesahs
------------------------------------------------------------------------
jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687
View this thread: http://www.excelforum.com/showthread...hreadid=503494

Your conditional formatting will only "hide" the errors but if that is
what you want to do then in A1,

Condition 1

Formula is: =ISERROR(A1)

Use Format white.

Make sure you do not have the $ signs around the cell reference. With
cell A1 selected, click on the Format Painter then click on the corner
of the header row/column intersection to select the whole sheet. This
will apply the format to all cells in the sheet.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=503494