IF formula output not expected

[LlebKcir]

I am using 2 embedded IF statements in the attempt to produce either a string (cell&cell...) or a string with -SPARE are the end.

I know the inner IF statement works as expected. The result is setup to work on the FALSE, not TRUE for desired output.

My outer IF works when I do not include the inner IF, so I am guessing I have something out of alignment, or I am just not performing the correct embedded IF sequence.

=IF('Table'!F23="TRUE",IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56),L56&"-BI"&M56&"-"&C56&"-SPARE")

The inner

IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56)

Will string&-SPARE the line when cell B17 is full of # marks, else will populate with correct values.

The otter loop:

IF('Table'!F23="TRUE",L56&"-BI"&M56&"-"&C56,L56&"-BI"&M56&"-"&C56&"-SPARE")

Also will check correctly and either populate the string, or populate string&-SPARE as expected.

When I combine them the only output is string&-SPARE, that is not my desired outcome.

Thank you in advance for the help.

 

[Fluff]

How about
=L56&"-BI"&M56&"-"&C56&IF(Table!F23=TRUE,IF($B$17="########","-SPARE",""),"-SPARE")

 

[Gerald Higgins]

The inner

IF($B$17="########",L56&"-BI"&M56&"-"&C56&"-SPARE",L56&"-BI"&M56&"-"&C56)

Will string&-SPARE the line when cell B17 is full of # marks, else will populate with correct values.

Really ? I find that hard to believe.
That formula tests whether B17 contains the text string "########", so should evaluate to FALSE if B17 contains the text strings "#######" or "#########" or "###" for example.
If B17 is really just full of #marks , that is usually an indication that B17 contains a number of some kind (perhaps a time or date) that Excel can not display correctly because the column width is too small.
And that can normally be addressed by making the column wider.
If that's what you mean, then
IF($B$17="########"
doesn't evaluate to TRUE in that situation.

 

[LlebKcir]

Fluffy, that was perfect. Not sure why changing the order like that works, but it is exactly what I was after.

Really ? I find that hard to believe.
That formula tests whether B17 contains the text string "########", so should evaluate to FALSE if B17 contains the text strings "#######" or "#########" or "###" for example.
If B17 is really just full of #marks , that is usually an indication that B17 contains a number of some kind (perhaps a time or date) that Excel can not display correctly because the column width is too small.
And that can normally be addressed by making the column wider.
If that's what you mean, then
IF($B$17="########"
doesn't evaluate to TRUE in that situation.

Higgins, yes I truly do me #### that is a bit of hard code we use on a reference worksheet to indicate null value for that cell. if that cell is null on the reference sheet we want the string&-SPARE on all sheets pointing to that value.

 

[Fluff]

The problem with your formula was that you had True in quotes which means it was looking for a text value, rather than a logical value.
I just took the opportunity to simplify the formula at the same time.

 

[LlebKcir]

I want to make sure I am following my error.

by having "TRUE" or "FALSE" that can lead to valuation errors as those are treated as text not Boolean logic values
Question, with text values could that lead to formulas not updating after a copy/paste value only?

 

[Fluff]

by having "TRUE" or "FALSE" that can lead to valuation errors as those are treated as text not Boolean logic values

That's right.
Copy/paste a values is fine, because the value will still be a boolean value, rather than text.

 

[LlebKcir]

Thank you, I will adjust my formulas and hopefully that will reduce the lack of updating I am seeing when I copy/paste values only. My formulas do not update currently with "TRUE"/"FALSE" unless I type the values in a 2nd time. very much defeats the reason for copy/paste values IMHO.

edit to add: Made the change from text to Boolean, poof problem solved.

Thank you again Fluffy

 

[Fluff]

You're welcome & thanks for the feedback