Indirect/iferror problem

[Erdrick]

This code runs just fine as a formula in a cell and it runs just fine in conditional formatting as long as i don't wrap an IFERROR around it. Once i wrap the IFERROR around it the comparison fails. The ADDRESS function has the correct syntax. I have verified that enter that as a formula in a cell.

VALUE(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Sheet1")))>18.7

Any help would be appreciated.

 

[taurean]

Like this perhaps:
=VALUE(IFERROR(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Sheet1")),0))>18.7

 

[Erdrick]

Like this perhaps:
=VALUE(IFERROR(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Sheet1")),0))>18.7

Thanks for the response. That was just the part that errored out. Here is the full condition. So I need to test if the data in the cell has a delimiter and they return the portion that does. Search and Find both return an error if search param is not found so I have to use an IFERROR statement to trap that and go a different direction is the delimited is not found. I am really baffled by this. I am positive that my syntax is correct, INDIRECT just doesn't seem to compare right once I wrap the IFERROR around it. The code run fine with the IFERROR state in a conditional format.


I spread this out to make it easier to read.

IFERROR(
if(Setup!$G$3="Miles",
IFERROR(VALUE(Left(N2, Len(N2) - (SEARCH("|",N2)+1)))<=VALUE(INDIRECT("'Vendors'!$D$"&(COLUMN()-12))),VALUE(N2)<=VALUE(INDIRECT("'Vendors'!$D$"&((COLUMN()-13)+1))))
,
IFERROR((value(Mid(N2, Find("*:*",N2), Len(N2)) + (Left(N2, Len(N2) - (SEARCH(":",N2)+1)) * 60))<=VALUE(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Vendors")))),FALSE)
)
,FALSE)

 

[taurean]

So now it is not giving intended results?
Can you describe what you want to achieve
e.g.
1. Check if Setup!G3 is equal to "Miles"
2. If condition #1 is true then check with "|" delimiter
Then check....
3. If condition #1 is false then check with "*:*" delimiter
Then check...

 

[Erdrick]

I ended up creating a UDF to work around this.. although I don't understand why that code wouldn't work.

 

[Erdrick]

If Setup!$G$3="Miles" then
SEARCH for "|" delimiter
else
SEARCH for ":" delimiter

The search function returns an error if the string it not found. Some cell would be formatted with a pipe "|", others will be formatted with a colon ":".

When I incorporate the IFERROR statement, the INDIRECT statement doesn't return the value, as I have confirmed it does with out the IFERROR statement. If I replace the indirect statement with a standard numeric value, say 50, the IFERROR statement function properly. The search statement can be completely removed to test this, as I did in the code below. Keep in mind, this runs find when entered as a cell formula, the issue is incorporating it into conditional formatting.

COLUMN() starts at column N and goes higher. The purpose of that is to get data from a single column from a different sheet.

IFERROR(
if(Setup!$G$3="Miles", 
IFERROR(20<=VALUE(INDIRECT("'Vendors'!$D$"&(COLUMN()-12))),VALUE(N2)<=VALUE(INDIRECT("'Vendors'!$D$"&((COLUMN()-13)+1))))
,
IFERROR(50<=VALUE(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Vendors"))),FALSE)
)
,FALSE)

Same problem with INDIRECT not using the ADDRESS function

IFERROR(
if(Setup!$G$3="Miles", 
IFERROR(20<=VALUE(INDIRECT("'Vendors'!$D$"&(COLUMN()-12))),VALUE(N2)<=)VALUE(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Vendors")))
 ,
IFERROR(50<=VALUE(INDIRECT(ADDRESS(COLUMN()-12,4,3,TRUE,"Vendors"))),FALSE)
 )
 ,FALSE)

 

[taurean]

OK. I assume UDF is working for you. Do you still want to work with standard functions and resolve it?

 

[Erdrick]

OK. I assume UDF is working for you. Do you still want to work with standard functions and resolve it?

At this point, I will just stick with the UDF. Although, I am curious to know if anyone else has ran into this issue. Perhaps as time goes on someone may see this thread.

Thanks Taurean.

 

[FDibbins]

Have not read all the posts, but keep in mind that CF works only on TRUE/FALSE (1/0), so Im not really sure you need to use IFERROR anyway. All IFERROR does is check your formula fo an error - if it finds an error, it triggers the 2nd argument, if there is no error, it used the formula.

You just need to structure the formula to return T/F (or 1/0) and that's is.