Vlookup

[matthewlouis]

Column R18 uses the formula =IFERROR(IF(D18<$H$3,"Failed",""),"")

D = current price

$H$3 is the price I am comparing D18 to. $H$3 is constant, of course

Once the price in Column D becomes LESS THAN the fixed price of $H$3, the cell in R18 says "FAILED." That's great. But when I copy the formula down and the cells in Column D become > than $H$3 again, Column D's price shows up again . . . I don't want that. Once a cell in Column R says "FAILED, I want every Column R cell AFTER that to say "FAILED" regardless if any of the following prices in Column D show up > $H$3 somewhere down the column.

This is messing up my Lookup formula in R4 =IFERROR(VLOOKUP("Failed",$R$6:$R$133,1,FALSE),"") because it's looking up everything AFTER R18 and including it in the Lookup.

Any ideas? Thanks in advance!!

 

[matthewlouis]

Hi Joe,

xlqhigh is the XLQ program's formula for returning the high from stock symbol from yahoo

My VBA knowledge is very limited but I was able to paste your code in VBA, ran what you said for B20, and it returned
[TABLE="width: 213"]
<tbody>[TR]
[TD="class: xl108, width: 213"]35-69-82-82-79-82[/TD]
[/TR]
</tbody>[/TABLE]

Does this tell you anything? Thanks for all your help on this!

 

[matthewlouis]

Thank you Crystal, sorry I couldn't show the sheet . . . xlq just won;t work with anyone else without a subscription so you wouldn't be able to see the sheet formulas. Appreciate your help on this!!

 

[Joe4]

xlqhigh is the formula for returning the high from yahoo on the symbol.

Where/how is that formula defined?
Can you post the actual formula behind xlqhigh?

I do not know how to do VBA but I pasted your code in VBA, ran what you said for B20 it returned
[TABLE="class: cms_table, width: 213"]
<tbody>[TR]
[TD="class: cms_table_xl108, width: 213"]35-69-82-82-79-82[/TD]
[/TR]
</tbody>[/TABLE]

That tells me that that the following is actually in cell B20:
#ERROR
which is not blank, but appears to be some sort of error code.

If you are not seeing anything appearing in that cell, then you probably have something like Conditional Formatting hiding the error result.
Can you check to see what kind of Conditional Formatting or Custom Formatting is being applied to cell B20?

 

[matthewlouis]

Good call, Joe. Yes, I just deleted a conditional format in B20 and #ERROR shows up. You are correct, there is something in that cell.

I just used =IFERROR(IF(A20=$H$3,xlqHigh($A$1,"Yahoo"),xlqhHigh($A$1,A20,"Yahoo")),"") to see if that would make the cell blank . . . no, it still shows #ERROR

Note that in that formula it is saying if today's date ($H$3) is = to A20 (date), then show today's high for the S&P 500. If A20 is not today, then show the high for the date in Column A. That's what xlqh is . . . it adds the h to xlq to tell you the date is not today.

 

[Joe4]

I just deleted a conditional format in B20 and #ERROR shows up.

What was the exact Conditional Formatting formula?
We should be able to employ the same logic in your formula.

 

[matthewlouis]

Not much of a formula . . . simple . . . If the cell has #ERROR , make the color white (so it would not be visible) -- a terrible way to condition that cell, I know now.

It would seem I need a formula (not a condition) that would make the cell blank for those dates in the future, no?

 

[Joe4]

I wonder if that formula is actually returning the literal text #ERROR .
What does this formula return?
=B20="#ERROR "

If it returns TRUE, then you can keep everything as it was, and just update your original formula to check to see if B20 equals "#ERROR ".
If you need help updated that original formula, please let us know what you what you would like the original formula to return when this happens.

 

[matthewlouis]

OK, put that conditional formula =B20="#ERROR " in there and it does NOT return True . . . returns #ERROR again.

I would need that cell to be blank since it says "#ERROR and then I believe that orginal formula will work.

 

[Joe4]

Does this return TRUE?
=ISERROR(B20)

What does this return?
=IFERROR(B20,"Error found!")

 

[matthewlouis]

Does this return TRUE?
=ISERROR(B20)
This returns FALSE

What does this return?
=IFERROR(B20,"Error found!")
This returns a blank