IF Function Nesting Problem

**jesahs** · 01-20-2006, 04:48 PM

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

Duke Carey · 01-20-2006, 05:15 PM

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" 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
>
>

**jesahs** · 01-20-2006, 05:22 PM

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?

It would be something like this in the conditional formatting formula box:

=ISERROR($A:$Z)

**jesahs** · 01-20-2006, 07:03 PM

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.

Duke Carey · 01-23-2006, 03:35 PM

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
>
>

**SteveG** · 01-23-2006, 04:03 PM

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** · 01-23-2006, 04:32 PM

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** · 01-23-2006, 04:40 PM

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