Nested IF with blank Value needed

[RandomWittyName]

Hello,

First and foremost, sorry if the title is confusing. I've been writing and staring at formulas so long words make little sense now. I work at a call center and am trying to write a formula for a workbook and am having some difficulty. Scenario: The work book has 11 pages. One per call agent and one for the supervisor. The call agent stats are entered in their sheets and reported on the supervisor sheet based on the month selected. The formula I'm using is all nested IFs:

=IF($Y$3="January",'Emp 4'!$B$13,IF($Y$3="February",'Emp 4'!$B$14,IF($Y$3="March",'Emp 4'!$B$15,IF($Y$3="April",'Emp 4'!$B$16,IF($Y$3="May",'Emp 4'!$B$17,IF($Y$3="June",'Emp 4'!$B$18,IF($Y$3="July",'Emp 4'!$B$19,IF($Y$3="August",'Emp 4'!$B$20,IF($Y$3="September",'Emp 4'!$B$21,IF($Y$3="October",'Emp 4'!$B$22,IF($Y$3="November",'Emp 4'!$B$23,IF($Y$3="December",'Emp 4'!$B$24))))))))))))

The trouble I'm having is that I need the formula to return a blank cell if no data was entered in the call agent's sheet. Normally, and if I didn't have to use the month as the variable, I'd use:

=IF($Y$3="January",'Emp 4'!$B$13," ")

But since I need it to adjust by the month, I'm stuck with the long nested IF formula. I can't figure this out! Can anyone help?

 

[wideboydixon]

Try this:

=IFNA(INDEX('Emp 4'!$B$13:$B$24,MATCH($Y$3,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)),"")

or this as an array formula (Ctrl+Shift+Enter):

{=IFNA(INDEX('Emp 4'!$B$13:$B$24,MATCH($Y$3,TEXT(DATE(2016,ROW($A$1:$A$12),1),"mmmm"),0)),"")}

WBD

 

[RandomWittyName]

Neither worked. The first gave a general error and the second denied. Our office has a very tight grip on the admin settings so a lot of normal functions are restricted.

Try this:

=IFNA(INDEX('Emp 4'!$B$13:$B$24,MATCH($Y$3,{"January","February","March","April","May","June","July","August","September","October","November","December"},0)),"")

or this as an array formula (Ctrl+Shift+Enter):

{=IFNA(INDEX('Emp 4'!$B$13:$B$24,MATCH($Y$3,TEXT(DATE(2016,ROW($A$1:$A$12),1),"mmmm"),0)),"")}

WBD

 

[wideboydixon]

I've never heard of Excel formulas being forbidden by some administrative setting. Are you sure that's the case? What are the actual error messages you get?

WBD

 

[ukmikeb]

Try :-

=IFERROR(INDEX('Emp 4'!B13:B24,MATCH($Y$3,TEXT(DATE(2016,ROW($1:$12),1),"mmmm"),0)),"")

entered with Control-Shift-Enter for Excel versions 2007 and later.

hth

@WideBoyDixon IFNA is not a valid function, certainly in Excel 2007