| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Function Nesting Problem
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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? -- jesahs ------------------------------------------------------------------------ jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687 View this thread: http://www.excelforum.com/showthread...hreadid=503494 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
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 -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=503494 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
Can I send you a sample of my worksheet to look at? -- jesahs ------------------------------------------------------------------------ jesahs's Profile: http://www.excelforum.com/member.php...o&userid=30687 View this thread: http://www.excelforum.com/showthread...hreadid=503494 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
|
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
IF Function Nesting Problem
I have replied to your other posting - you may have set all cells to
white. Pete |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Problem occur in sum function while deleting the rows | Excel Worksheet Functions | |||
| Problem with IF function | Excel Worksheet Functions | |||
| Solver problem where 'IF Function' is bad | Excel Discussion (Misc queries) | |||
| IF function problem | Excel Worksheet Functions | |||
| How do I nesting subtotal function within average function in Exc | Excel Worksheet Functions | |||
Linear Mode
