Complicated formula giving

[aerospace1]

Hello, I am trying to figure out why Excel is giving me errors about the new formulas that I am creating. I have an original formula that works and gives me updates:
=IF($B$3="(All)",CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code ",$B$3," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1))


I am trying to also add $B$4 on to it as well. I've tried multiple ways of doing this and it is either saying "#value" or "You've entered too many arguments for this function. Down below would be the multiple ways that I have done it:

=OR(IF($B$3="(All)",CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code ",$B$3," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)),IF($B$4="(All)",CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code ",$B$4," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)))

=IF(OR($B$3,$B$4)="(All)",CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code ",OR($B$3,$B$4)," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1))


EDIT:
Some more context: Is there a way to include $B$4? This formula is to populate a chart title that I created. For example, I am working with 2 slicers that changes the title of the graph based on what I have selected. I am also trying to include a 3rd slicer (that is where $B$4 comes into play) to work with in the title. Any solutions?

 

[Special-K99]

Your logic is not clear from your description
and there's a much easier way of doing this that shortens the formulas drastically.
But you need to define your logic.

Ignore the cumbersome phrase "Top Skills..." etc

In your original formula
IF B3 = ALL then you want B2 displayed otherwise you want B3

So what do you want if B4 is ALL ?

 

[aerospace1]

Sorry about that.

If B4 is ALL then I would want B3 to be displayed Otherwise I would want B4 to be displayed.

For example: There is a Directorate number (B2), Organization number (B3), and Branch number (B4).

Top of the funnel will be the directorate number chosen, then Organization number and bottom of the funnel will be Branch Number.

Thanks for your response.

Your logic is not clear from your description
and there's a much easier way of doing this that shortens the formulas drastically.
But you need to define your logic.

Ignore the cumbersome phrase "Top Skills..." etc

In your original formula
IF B3 = ALL then you want B2 displayed otherwise you want B3

So what do you want if B4 is ALL ?

 

[Special-K99]

What happens if netiher B3 nor B4 are ALL (see "SOMETHING ELSE" below)?

Try

=CONCATENATE("Code ",IF($B$3="(All)",$B$2,IF($B$4="(All)",$B$3),"SOMETHING ELSE"))," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)

 

[aerospace1]

Hey Thanks,

I tried doing this and it is saying that "There are too many arguments happening. In another forum someone mentioned to use this formula instead, but it still does not work.

[FONT=&quot]=IF(OR([/FONT][FONT=&quot]$B$3="(All)",$B$4="(<wbr>All)"),CONCATENATE("Code ",$B$2," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),IF($B$3<>"(All)",C<wbr>ONCATENATE("Code ",$B$3," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1),CONCATENATE("Code ",$B$4," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)))

[/FONT][FONT=&quot]So I have three slicers that I am using for the graph. Directorate is the first one. Next comes Division, and then Branch. it's like a funnel system.[/FONT]

1. Directorate

[FONT=&quot]2. Division[/FONT]
[FONT=&quot]3.Branch [/FONT]
[FONT=&quot]When I am using the Directorate slicer, the numbers populate in the chart, which is perfect. But when I use Division, the chart does not populate with the Division number. It still stays with the Directorate number. Then once I use the Branch slicer, the chart populates with the Division number. I am a bit confused why this happens. [/FONT]
[FONT=&quot]For Example: I pick 400, in the directorate slicer. The chart populates as "400". Then the Division slicer populates with all of the codes under 400. I select Code 450 under the Division slicer. The chart does not populate as "450" but as "400". I then go to the Branch slicer and pick 450.2. While choosing that, the chart now populates as "450" instead of 450.2. I hope this helps. [/FONT]

What happens if netiher B3 nor B4 are ALL (see "SOMETHING ELSE" below)?

Try

=CONCATENATE("Code ",IF($B$3="(All)",$B$2,IF($B$4="(All)",$B$3),"SOMETHING ELSE"))," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)

 

[Special-K99]

My bad, too many brackets
Try

=CONCATENATE("Code ",IF($B$3="(All)",$B$2,IF($B$4="(All)",$B$3,"SOMETHING ELSE"))," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)

 

[aerospace1]

It works! Thank you so much.

There is one thing that happens with that formula that I noticed. if someone picks a certain Directorate and skips the Division slicer and uses the Branch slicer, the chart only populates as the Directorate number. Is there a way to fix that?

My bad, too many brackets
Try

=CONCATENATE("Code ",IF($B$3="(All)",$B$2,IF($B$4="(All)",$B$3,"SOMETHING ELSE"))," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)

 

[Special-K99]

Fix it how?
Are you saying they shouldnt enter be able to enter B2 and B4 without entering something in B3, or they can but you want to see a different result? If so, which result?

 

[aerospace1]

Sorry if what I am saying is confusing.

They should be able to pick B4 to show up on the charts name without picking B3. Some people might just skip the B3 (Division slicer) all together. When that happens, B2 shows up as the charts name. I would like it to show the result of B4.

Fix it how?
Are you saying they shouldnt enter be able to enter B2 and B4 without entering something in B3, or they can but you want to see a different result? If so, which result?

 

[Special-K99]

Try untested as I don't fully understand this

=CONCATENATE("Code ",IF($B$3="(All)",$B$2,IF($B$3="",$B$4,IF($B$4="(All)",$B$3,"SOMETHING ELSE")))," - Top Skills & Retirement Eligibility as of "&'Date for Charts'!C1)