More =IF formula issues

LlebKcir

Board Regular
Joined
Oct 8, 2018
Messages
219
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.

Code:
=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?
 
Last edited by a moderator:
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.
 
Upvote 0

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
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
 
Last edited:
Upvote 0
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:
Code:
=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:

Code:
 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...)
 
Upvote 0
Try replacing
Code:
 ws.Range("D3:H52").Value2 = ws.Range("D3:H52").Value2

with
Code:
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
 
Last edited:
Upvote 0
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

Code:
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
 
Last edited:
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top