More =IF formula issues

[LlebKcir]

A simple =IF statement checks a value in a cell on a different worksheet for TRUE/FALSE and performs the string paste accordingly. I have removed the "" from around the boolean TRUE/FALSE.

The boolean values are copy/paste value from another workbook. Some formulas will update, many will not until I manually retype the TRUE/FALSE values into the cells, and even after that there are still formulas that refuse to update.

=IF('Table'!G$20=TRUE,$B$4&"_AI"&M26&"-"&C26&" "&F26,$B$4&"_AI"&M26&"-"&C26&" "&F26&"-SPARE")

I have manually types TRUE into Table G20 multiple times, the above formula is still evaluating FALSE and placing the -SPARE at the end of the string.

=IF('Table'!G20=TRUE,"YES","NO")

I just ran that in a blank unused cell, it is evaluating to NO. That is the incorrect output. Table G20 clearly has TRUE typed in that cell and it is formatted General.

side note: After typing true and not TRUE in the cell allowing for Excel to convert to boolean and force to uppercase, the formula functions as expected and removes the -SPARE from the string and the short test code evaluates to YES.

What is wrong with Excel and my formula?

 

[LlebKcir]

If you are getting inconsisitent values in cells (sometimes boolean, sometime text), then how about using this method ...

=IF(OR('Table'!G20=TRUE,'Table'!G20="TRUE"),"YES","NO")

That would be a work-around, but still does not solve the issue with Excel changing the values from boolean to text when copy/paste value is used. Stupid to rebuild 1000+ formulas because Excel cannot get its act together. A boolean is a boolean, there is zero reason for that to be changed by Excel.

 

[Yongle]

Stupid to rebuild 1000+ formulas

It's a workaround - and makes the problem go away give you a change to deal with the problem
(How much time does it take to amend a few formulas compared to the fallout of not doing so?)

still does not solve the issue

agreed
- "it sweeps things under the carpet" rather than correcting the cause

Excel cannot get its act together

- Excel is NOT the problem here
- Excel is always consistent even if we cannot see the pattern
- it is something that you are doing or VBA is doing

The underlying cause
- how is the original TRUE value (which ends up in G20 etc) created in the original sheet ? User input, formula ? etc
- is that value always a BOOLEAN in the original sheet ?
- please paste the VBA placing that value in sheet "Table" into your reply

 

[LlebKcir]

It's a workaround - and makes the problem go away give you a change to deal with the problem
(How much time does it take to amend a few formulas compared to the fallout of not doing so?)


agreed
- "it sweeps things under the carpet" rather than correcting the cause


- Excel is NOT the problem here
- Excel is always consistent even if we cannot see the pattern
- it is something that you are doing or VBA is doing

The underlying cause
- how is the original TRUE value (which ends up in G20 etc) created in the original sheet ? User input, formula ? etc
- is that value always a BOOLEAN in the original sheet ?
- please paste the VBA placing that value in sheet "Table" into your reply

The array is as follows:

=IF(ISBLANK(IFERROR(INDEX(INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$A$1:$CZ$400")),SMALL(IF(1=((--($G$1=INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$G$1:$G$400"))))*(--($C$1=INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$B$1:$B$400"))))),ROW(INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$A$1:$CZ$400"))),""),COLUMN()-3),$K3),"")),"",IFERROR(INDEX(INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$A$1:$CZ$400")),SMALL(IF(1=((--($G$1=INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$G$1:$G$400"))))*(--($C$1=INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$B$1:$B$400"))))),ROW(INDIRECT(CONCATENATE("'[Master.Spreadsheet-13-Feb-2019.xlsm]",IF(IFERROR(SEARCH(" - ",$C$1),0),LEFT($C$1,FIND(" - ",$C$1,1)-1),$C$1),"'!$A$1:$CZ$400"))),""),COLUMN()-3),$K3),""))

The VB code that replaces the output of the array with the data is as follows:

 ws.Range("D3:H52").Value2 = ws.Range("D3:H52").Value2

As posted above, the output from =cell("type",g20) was v on the workbook AFTER the vb code converts the array with the .value=.value.

The value pulled from the Master.Spreadsheet is boolean. The process to get the value from workbook A into workbook B is select data in workbook A, copy data in workbook A, select workbook B, right click paste value into workbook B to preserve formatting (font size, font type, bold, center, etc...)

 

[Yongle]

Try replacing

 ws.Range("D3:H52").Value2 = ws.Range("D3:H52").Value2

with

With ws.Range("D3:H52")
    .NumberFormat = "General"
    .Value2 = .Value2
End With

Tested with
-boolean TRUE
- string TRUE
- value typed directly into cell
- value returned by formula

EVERY value was converted to boolean by the above code

 

[LlebKcir]

Yongle, I will try that this morning. Thank you kindly.

 

[Yongle]

Reading my previous post again, and thinking it through, perhaps I should have suggested ....

If the values that are causing the problem are in column D and you do not want to use general format in other columns then

With ws.Range("[COLOR=#006400]E[/COLOR]3:H52")
    .Value2 = .Value2
End With

With ws.Range("[COLOR=#ff0000]D[/COLOR]3:[COLOR=#ff0000]D[/COLOR]52")
    .NumberFormat = "General"
    .Value2 = .Value2
End With

 

[LlebKcir]

The correct range is D3:H52. Not a massive section, but all either True or False values that should be read as boolean values over that range.

 

[LlebKcir]

Thank you Yongle, that worked.

 

[Yongle]

Thanks for your feedback