IF Function Nesting Problem

[jesahs]

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

 

[Guest]

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,TrackingGantt!$B:$B,FALSE),FALSE))

 

[jesahs]

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]

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.

 

[Guest]

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.

 

[SteveG]

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

 

[jesahs]

Alright, I got it to work, but it makes *every *cell white. I have cells
peppered throughout the sheet that need to be certain colors depending
on number inside.

Can I not pick and choose which cells to make blank, i.e. only cells
that have an error, rather than the whole sheet?

 

[SteveG]

That will only format cells with errors white, not cells without errors
(I tested on my own spreadsheet). Since we don't know what your data
consists of, it makes it difficult to ensure a solution will workk for
you.

Seeing an example of your sheet would be helpful as well as ALL the
conditions that you are looking to apply. CF can be tricky if you
don't enter in the conditions in the correct order when using
multiple.


Steve

 

[jesahs]

Can I send you a sample of my worksheet to look at?

 

[SteveG]

Yes.


(e-mail address removed)

 

[Pete]

I have replied to your other posting - you may have set all cells to
white.

Pete