Function that references a cell but the referencing cell's value isn't being used when it's evaluating the function and the cells name is being used
I'm producing an excel file .xls with Gembox, and it's required to be .xls, and I get a file. In it I have a cell with a function in it but it's returning false, but if I copy the function to a new cell it returns true. Also if I click in the function bar and out it also returns true. So I went to evaluate the formula and get this.
As you can see it's initially trying to evaluate the part ISTEXT(D48) as an entire step at once, skipping the reference to the other cell, which fails. But if you look at it after I've clicked in it does this.
Which it's first evaluating the reference and then correctly going to check if it's text. So I want this
Thing is I have thousands of cells with formulas that are behaving this way, so going in and clicking in them all isn't feasible, and there's too many columns to make text to columns on every column feasible either. Preferably I'd like it to just work once downloaded. Secondly being able to fix them all at once would be satisfactory.
The calculation is set to automatic. Calculate now and calculate sheet don't do anything. Cause the function is "calculating" it's just that the checks to make sure the fields are proper values are failing cause it's not referencing the cell's value.
EDIT:
Here's another function in the workbook having an issue. =IF(AND(DD14<>1,ISNUMBER(SEARCH("nc",U14))),1,"") The SEARCH("nc",U14) is again being evaluated as is and not replacing the U14 with the value in U14 before running the search function
[link] [comments]
Want to read more?
Check out the full article on the original site